当前位置: > 数据库 > SQL Server 2005 >

SQL SERVER 2005中的日期时间类型

时间:2015-10-10 15:50来源:csdn.net 作者:IT

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

(责任编辑:IT)
------分隔线----------------------------