SQL Server 学习笔记之日期和时间数据类型

数据类型	格式	范围	精确度	存储大小（字节单位）
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