SQL Server中的日期时间类型

一． 数据类型
SQL Server008在上一个版本的基础上把日期时间类的数据类型增加到了六个。除了SQL Server2005的datetime和smalltime之外，加了date，datetime2，time和datetimeoffset。
datetime
数据库内部用两个4字节的整数存储datetime数据类型的值。第一个4字节存储基础日期（即1900-1-1, base date）之前或之后的日期。日期范围为1753-1-1至9999-12-31。当日期为1900-1-1时值为0；之前的日期是负数；之后日期是正数。另外一个4字节存储以午夜后三又三分之一（10/3）毫秒数所代表的每天的时间。精确度为百分之三秒（等于3.33毫秒或0.00333秒，至于为什么选择3.33毫秒的原因见下）。如下表所示，把值调整到.000、.003、或.007秒的增量。

由于datetime的单位是（10/3）毫秒，因此若要指定第二个4字节的时间值，需要乘上单位（10/3），比如，指定第二个4字节的值为10000的时间，我们需要如下的操作：
DECLARE @D1 DATETIME, @D2 DATETIME
SET @D1 = DATEADD(MS, 10000 * 10 / 3, 0)
SET @D2 = DATEADD(MS, 10000, 0)
SELECT @D1, @D2
–1900-01-01 00:00:33.333 1900-01-01 00:00:10.000

查询中，我们常常需要搜索指定日期范围内的数据，比如返回1998-01-01当天内的数据，你可能会这样写：
Date >= ‘1998-01-01 00:00:00.000’and date <= ‘1998-01-01 23:59:59.999’

根据上面的调整规则，其实这句语句的实际搜索范围为：
Date >= ‘1998-01-01 00:00:00.000’ and date <= ‘1998-01-02 00:00:00.000’

你会看到这包括了1998-01-02的数据，所以最好的正确的搜索语句为：
Date >= ‘1998-01-01 00:00:00.000’and date < ‘1998-01-02 00:00:00.000’

我们可以使用下面的代码来显示datetime类型是如何保存的（下面的smalldatetime方法同）：
declare @dt datetime
set @dt = getdate()
select convert(int, substring(convert(varbinary(8), @dt), 1, 4)), datediff(day, 0, @dt)
select convert(int, substring(convert(varbinary(8), @dt), 5, 8)), datediff(ms, dateadd(day, datediff(day, 0, @dt), 0), @dt) * 3 / 10.0

smalldatetime
smalldatetime数据类型存储日期和每天的时间，但精确度低于datetime。SQL Server将smalldatetime的值存储为两个2字节的整数。第一个2字节存储1900-1-1后的天数。另外一个2字节存储午夜后的分钟数。日期范围从1900-1-1到2079-6-6，时间精确到分钟。29.998秒或更低的smalldatetime值向下舍入为最接近的分钟，29.999秒或更高的smalldatetime值向上舍入为最接近的分钟。
–returns time as 12:35
SELECT CAST(‘2000-05-08 12:35:29.998’ AS smalldatetime)
GO
–returns time as 12:36
SELECT CAST(‘2000-05-08 12:35:29.999’ AS smalldatetime)
GO

注意：我以前的曾经了翻译一篇文章，讲述了一些在计算机中常用的表示时间的方法。这些时间表示方法都是以一个时间作比照点，记录到指定时间的时间（比如秒、100毫秒等等）数。这和SQL SERVER中datetime/smalldatetime表示方法完全不同。
Datetime的精确度为什么是3.33毫秒？
注意：本问题目前还没有确切答案。
我看到这个问题的第一个答案就是：空间不够呗，所以只能精确到3.33毫秒。
是这样的吗？假如精确到1毫秒，则一天的毫秒数为：24 * 60 * 60 * 1000 = 86400000，而四个字节最大的数为2^32（42 9496 7296），这个数字远远大于8640 0000。所以肯定不是存储空间不够的原因。
但是有一点可以确定的：现在的SQL SERVER一定为了兼容原来版本才会选择3.33毫秒这个精确度的。因为我发现SYBASE中的DATETIME的存储格式和SQL SERVER一模一样，精确度也是3.33毫秒。众所周知，他们两个是有共同的祖先，是后来才分开的。
那么当初SYBASE为什么会选择3.33毫秒的呢？这就要涉及到一些历史因素了， 比如当初CPU频率、当初的存储空间少等情况了，只能猜测罢了[5]。

二． 日期时间的赋值
上面说了格式，当然我们是没有办法直接赋整数给日期时间变量的。给这些变量赋值通常是给它指定个一定格式的字符串。SQL SERVER会自动将字符串转换成日期格式保存的，注意：数据库中是不会保存数据格式的字符串的。下面几种日期格式的字符串，SQL SERVER会非常轻易就“认出”的。
1） ISO 8061格式
ISO时间格式：yyyy-mm-dd hh:mm:ss[.mmm]，必须指定每一个元素，只有毫秒是可选的，时间成分以24小时格式指定。
使用 ISO 8601 格式的优势在于它是一个国际标准。另外，使用此格式指定的日期时间值很明确。同时，此格式不受 set dateformat设置或 set language设置的影响。

2） 字母日期格式
在 SQL Server 2008 中也可以以当前语言给出的月的全名（如 April）或月的缩写（如 Apr）来指定日期数据；逗号是可选的，而且忽略大小写。

这种日期格式只有在制定的语言中才能起作用。我们可以通过调用存储过程sp_helplanguage来查看SQL SERVER支持的所有语言以及这些语言的月份全名和简称。我们同时可以发现有三种语言：简体中文，韩语（한국어）和日语（日本語）的月份名称是数字而不是字母，因此在这三种语言中是不支持字母日期格式的。

以下是使用字母日期格式的一些原则：
a.把日期和时间数据括在单引号中 (‘)。
b.下面是 SQL Server 日期数据的有效字母格式（括号内的字符是可选字符）：
Apr[il] [15][,] 1996
Apr[il] 15[,] [19]96
Apr[il] 1996 [15]

[15] Apr[il][,] 1996
15 Apr[il][,][19]96
15 [19]96 apr[il]
[15] 1996 apr[il]

1996 APR[IL] [15]
1996 [15] APR[IL]
注意 ，没有这样一种情况：省略日，缩写年份。如 Apr[il] [15][,] [19]96 这是错误的格式。

c.如果只指定年份的最后两位数字，则小于 [两位数年份截止]配置选项值最后两位数字的值与缩略形式的年份位于同一个世纪。大于或等于该选项值的值位于缩略形式年份的上一世纪。例如，如果[两位数年份截止]为 2050（默认值），则 25 被解释为 2025，50 被解释为 1950。为避免模糊不清，请使用四位数的年份。

[两位数年份截止]配置选项服务器属性中的高级配置下。该选项可以从1753 到 9999 之间指定一个整数来表示缩略形式的年份，以将两位数的年份解释为四位数的年份。
SQL Server 默认的时间范围是 1950-2049，表示截止年份为 2049。这说明 SQL Server 将两位数年份 49 解释为 2049 年，将两位数年份 50 解释为 1950 年，而将两位数年份 99 解释为 1999 年。若要维护向后兼容性，请将设置保持为默认值。

d.如果没有指定日，则默认值为当月第一天。
e.当按字母形式指定月时，SET dateformat 会话设置不起作用。

3） ODBC日期时间格式
ODBC API 定义了转义序列来表示日期和时间值，ODBC 称之为时间戳数据。用于 SQL Server 的 Microsoft OLE DB 访问接口所支持的 OLE DB 语言定义 (DBGUID-SQL) 也支持这种 ODBC 时间戳格式。使用 ADO、OLE DB 和基于 ODBC 的 API 的应用程序可以使用这种 ODBC 时间戳格式来表示日期和时间。
ODBC 时间戳的转义序列格式为：{ literal_type ‘constant_value’ }
literal_type 指定转义序列的类型。时间戳有三个 literal_type 说明符：
d = 仅日期
t = 仅时间
ts = 时间戳（时间 + 日期）
‘constant_value’
转义序列的值。constant_value 必须遵循每个 literal_type 的格式。
literal_type constant_value 格式
d yyyy-mm-dd
t hh:mm:ss[.fff]
ts yyyy-mm-dd hh:mm:ss[.fff]
这些是 ODBC 时间和日期常量的例子：
{ ts ‘1998-05-02 01:23:56.123’ }
{ d ‘1990-10-02’ }
{ t ‘13:33:41’ }
不要混淆 ODBC 和 OLE DB 时间戳数据类型名称与 Transact-SQL timestamp 数据类型名称。ODBC 和 OLE DB 时间戳数据类型记录日期和时间。Transact-SQL timestamp 数据类型是一个与时间值无关的二进制数据类型。

4） 分隔字符串格式
在 Microsoft SQL Server 2005 中，可以使用指定的数值月指定日期数据。例如，5/20/97 代表 1997 年 5 月 20 日。使用数值日期格式时，可在字符串中使用斜杠 (/)、连字符 (-) 或句点 (.) 作为分隔符指定月、日和年。此字符串必须使用以下格式：
number separator number separator number [time] [time]

这种格式常常会因为不同国家可能解释不同，比如像“01/02/03”这个日期格式：

像这种情况，我们若要直接赋值给日期变量，我们就可以使用SET DATEFORMAT来设置格式：参数包括 mdy、dmy、ymd、ydm、myd 和 dym。每种语言都有自己的缺省DATEFORMAT，可以通过sp_helplanguage来查看每种语言的日期格式。

下列是分隔字符串日期格式有效例子：
[0]4/15/[19]96 – (mdy)
[0]4-15-[19]96 – (mdy)
[0]4.15.[19]96 – (mdy)
[0]4/[19]96/15 – (myd)

15/[0]4/[19]96 – (dmy)
15/[19]96/[0]4 – (dym)
[19]96/15/[0]4 – (ydm)
[19]96/[0]4/15 – (ymd)

5） 未分隔字符串格式
Microsoft SQL Server 2005 允许您将日期数据指定为未分隔字符串。日期数据能够用 4、6 或 8 位数字、空字符串或不带日期值的时间值来指定。
SET DATEFORMAT会话设置并不适用于全数值日期项，例如不带分隔符的数值项。6 位或 8 位数字的字符串始终被解释为 ymd。月和日必须始终是 2 位数字。
下面是有效的未分隔字符串格式：[19]960415
只有 4 位数字的字符串被解释为年。月和日期被设置为 1 月 1 日。当只指定 4 位数字时，必须包括世纪。

6） 时间部分格式
上面说的日期格式重点谈了日期部分的格式，若日期格式的时间部分没有定义，那么SQL SERVER就将子夜（midnight）时间作为默认的时间值。
现在来看看时间部分的格式。SQL Server 2005 能够识别下列格式的时间数据。用单引号 (‘) 把每一种格式括起来。
14:30
14:30[:20:999]
14:30[:20.9]
4am
4 PM
[0]4[:30:20:500]AM

如果日期部分没有进行定义，那么SQL SERVER就将1900年1月1日作为默认的日期值。

可以用一个 AM 或 PM 后缀来表明时间值是在中午 12 点之前还是之后。AM 或 PM 的大小写可忽略。
小时可以用 12 小时或 24 小时的时钟来指定。小时值解释如下：
a. 小时值 0 表示午夜 (AM) 后的小时，不论是否指定了 AM。当小时值等于 0 时，不能指定 PM。
b. 如果未指定 AM 或 PM，小时值 1 到 11 表示中午以前的小时。当指定 AM 时，也表示中午以前的小时。当指定 PM 时，则表示中午以后的小时。
c. 如果未指定 AM 或 PM，小时值 12 表示始于中午的小时。如果指定 AM，则表示始于午夜的小时。如果指定 PM，则表示始于中午的小时。例如：12:01 是指中午过后 1 分钟，即 12:01 PM，而 12:01 AM 是指午夜过后 1 分钟。指定 12:01 AM 与指定 00:01 或 00:01 AM 相同。
d. 如果未指定 AM 或 PM，小时值 13 到 23 表示中午以后的小时。当指定 PM 时，也表示中午以后的小时。当小时值从 13 到 23 时，不能指定 AM。
e. 小时值 24 无效，用 12:00 AM 或 00:00 表示午夜。
可以在毫秒之前加上冒号 (:) 或者句号 (.)。如果前面加冒号，这个数字表示千分之一秒。如果前面加句号，单个数字表示十分之一秒，两个数字表示百分之一秒，三个数字表示千分之一秒。例 如，12:30:20:1 表示 12:30 过了 20 又千分之一秒；12:30:20.1 表示 12:30 过了 20 又十分之一秒。

上面说了SQL SERVER可以“自动识别”的所有日期时间类型。识别时可能要考虑当前的语言（试用set lanuage设置）和日期格式（试用set dateformat设置）的影响。你若厌倦于此，可以使用最后的“杀手锏”——CONVERT函数来显式转换，CONVERT的第三个参数对于日期格式和字符串格式定义如下：

三． 日期的输出
和日期的赋值不同，SQL SERVER不会“自动”识别你需要哪种输出格式。我们只有试用CONVERT函数来实现日期输出格式的控制。
当使用CONVERT函数处理日期时间的输出格式时，我们可以使用与处理输入数据时完全相同的风格设置，唯一的区别是：处理输出数据格式时，CONVERT函数将一个日期时间实例转换为一个字符串，而处理输入数据格式时，则是从一个字符串生成一个日期时间实例。

四． 时间函数
常用的时间函数如下：
DATEADD：可以对时间类型的指定部分进行加减计算。我们常常根据一个时间来构造出另外一个时间，比如下个月的今天,本月底等等，我们应该也尽量使用DATEADD函数来构造，它可以避免一些闰月、年底、月底之类的错误。对一个日期时间变量直接加减一个整数和使用DATEADD(DAY, n,@D)的功能是一样的。

DATEDIFF：该函数对两个时间变量对指定部分进行比较计算。此函数不考虑比指定日期部分更高的粒度级别，它只考虑更低级别的部分。对时间的比较应尽量使用本函数。使用DATEDIFF和DATEADD可以对日期时间变量进行“截尾”的操作（举例见下面的常用查询）。

DATEPART：返回日期时间变量的指定部分的值。
DATENAME：返回日期时间变量的指定部分的值，和DATAPART不同的是本函数返回的是个字符串类型。
GETDATE()返回本机器的当前时间。CURRENT_TIMESTAMP变量与本函数功能相同。
GETUTCDATE()返回本机器的当前UTC（格林尼治标准）时间。
DATEADD、DATEDIFF、DATEPART和DATENAME函数使用到一些共同的参数与缩写如下：

利用上面的函数，总结一些常用的查询（可以看看是如何进行“截尾”操作的）：
–本月开始，相当于将本月的日期“截尾”
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
–本年开始，相当于将本年的月份“截尾”
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
–本周一，相当于将本周的日期“截尾”
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)
–本季度开始，相当于将本季的日期“截尾”
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
–本月结束
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) -1
–本年结束
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) - 1
–本周结束
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) + 1, 0) - 1
–本季度结束
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) + 1, 0) - 1
–足年计算法一.
使用PUBS数据库中的EMPLOYEE表。（下同）
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, HIRE_DATE, GETDATE()), HIRE_DATE) > GETDATE() THEN 1
ELSE 0
END
FROM EMPLOYEE
–此法对于闰年的2月29日和平年的2月28日是相等的。

–足年计算法二.
SELECT , DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN 100 MONTH(HIRE_DATE) + DAY(HIRE_DATE) > 100 * MONTH(GETDATE()) + DAY(GETDATE()) THEN 1
ELSE 0
END
FROM EMPLOYEE

SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN SUBSTRING(CONVERT(VARCHAR(8), HIRE_DATE, 112), 5, 4) > SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112), 5, 4) THEN 1
ELSE 0
END, SUBSTRING(CONVERT(VARCHAR(8), HIRE_DATE, 112), 5, 4), SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112), 5, 4)
FROM EMPLOYEE
此法对于闰年的2月29日和平年的3月1日是相等的。

足年计算法三.
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN DATEPART(DAYOFYEAR, HIRE_DATE) > DATEPART(DAYOFYEAR, GETDATE()) THEN 1
ELSE 0
END
FROM EMPLOYEE
–此法比较简单，但是在闰年和平年3月份以后的日期时有1日的差别。

–足月计算法一.
SELECT *, DATEDIFF(MONTH, HIRE_DATE, GETDATE()) - CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, HIRE_DATE, GETDATE()), GETDATE()) > GETDATE() THEN 1
ELSE 0
END
FROM EMPLOYEE

–足月计算法二.
SELECT *, DATEDIFF(MONTH, HIRE_DATE, GETDATE()) - CASE WHEN DAY(HIRE_DATE) > DAY(GETDATE()) THEN 1
ELSE 0
END
FROM EMPLOYEE