SQL SERVER 2005中的日期时间类型

SQL SERVER 2005中的日期时间类型

最后编辑：2011-7-17

编辑历史：2011-7-17增加了3.33毫秒描述

一． 数据类型
MS SQL Server 2005有datetime和smalldatetime两种格式的日期时间数据类型。注意：没有单独的日期或时间类型。

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-ddT hh:mi:ss[.mmm]，必须指定每一个元素，只有毫秒是可选的，时间成分以24小时格式指定。
使用 ISO 8601 格式的优势在于它是一个国际标准。另外，使用此格式指定的日期时间值很明确。同时，此格式不受 SET DATEFORMAT 设置或 SET LANGUAGE 设置的影响。

2） 字母日期格式
在 Microsoft SQL Server 2005 中可以以当前语言给出的月的全名（如 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 之间指定一个整数来表示缩略形式的年份，以将两位数的年份解释为四位数的年份。
Microsoft 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）时间作为默认的时间值。
现在来看看时间部分的格式。Microsoft 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


参看书籍：

1.《Sql Server 2005编程艺术》
2.《SQL Server 2005技术内幕: T-SQL程序设计》
3.http://technet.microsoft.com/zh-cn/library/ms180878%28SQL.90%29.aspx
4.http://technet.microsoft.com/zh-cn/library/ms191004%28SQL.90%29.aspx
5.http://technet.microsoft.com/en-us/library/aa175784%28SQL.80%29.aspx