数据类型 | 格式 | 范围 | 精确度 | 存储大小(字节单位) |
---|---|---|---|---|
time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000~23:59:59:9999999 | 100纳秒 | 3~5 |
date | YYYY-MM-DD | 0001-01-01~9999-12-31 | 1天 | 3 |
smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01~2079-06-06 | 1分钟 | 4 |
datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01~9999-12-31 | 0.00333秒 | 8 |
datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000~9999-12-31 9999-12-31 23:59:59.9999999 | 100纳秒 | 6~8 |
datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn][+/-]hh:mm | 0001-01-01 00:00:00.0000000~9999-12-31 23:59:59.9999999(以UTC时间表示) | 100纳秒 | 8~10 |
执行代码:
SELECT
CAST('2009-10-28 12:35:29. 1234567 +12:15' AS time(7)) AS 'time',
CAST('2009-10-28 12:35:29. 1234567 +12:15' AS date) AS 'date',
CAST('2009-10-28 12:35:29.123' AS smalldatetime) AS 'smalldatetime',
CAST('2009-10-28 12:35:29.123' AS datetime) AS 'datetime',
CAST('2009-10-28 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2',
CAST('2009-10-28 12:35:29. 1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset';
结果:
值 | |
---|---|
time | 12:35:29.1234567 |
date | 2009-10-28 |
smalldatetime | 2009-10-28 12:35:00 |
datetime | 2009-10-28 12:35:29.123 |
datetime2 | 2009-10-28 12:35:29.1234567 |
datetimeoffset | 2009-10-28 12:35:29.1234567 +12:15 |