SQL Server2000日期时间的处理

【转自http://blog.csdn.net/longhorn_fu/archive/2005/09/02/470008.aspx】
日期是数据处理中经常使用到的信息之一。生日、数据处理时间、计划的预计完成时间，按年、季、月的统计，这些都属于日期处理的范畴。由于日期中包含了年、季、月、日等众多信息，不同的国家对日期格式、日期文字描述及星期有不同的规定，因此产生了日期处理的复杂性。本章主要讨论在SQL Server数据库中对日期的各种处理方法。

1.1 日期类型概述
SQL Server中的日期类型包括datetime和smalldatetime，仅能处理可以识别为1753年～9999年间的日期的值，没有单独的日期型或时间型。

1．datetime
datetime类型处理从1753年1月1日～9999年12月31日的日期和时间数据，精确度为百分之三秒。即：对于0.000～0.001、0.009的日期值，调整为0.000；对于0.002～0.004的日期值，调整为0.003；对于0.005～0.008的日期值，调整为0.007。

例如，下面的代码在输入时，其时间精确度为百分之一秒，但经数据库保存后再显示出来，其结果就已经做了处理。
DECLARE @t TABLE(date char(21))
INSERT @t SELECT '1900-1-1 00:00:00.000'
INSERT @t SELECT '1900-1-1 00:00:00.001'
INSERT @t SELECT '1900-1-1 00:00:00.009'
INSERT @t SELECT '1900-1-1 00:00:00.002'
INSERT @t SELECT '1900-1-1 00:00:00.003'
INSERT @t SELECT '1900-1-1 00:00:00.004'
INSERT @t SELECT '1900-1-1 00:00:00.005'
INSERT @t SELECT '1900-1-1 00:00:00.006'
INSERT @t SELECT '1900-1-1 00:00:00.007'
INSERT @t SELECT '1900-1-1 00:00:00.008'

SELECT date,转换后的日期=CAST(date as datetime) FROM @t

/*--结果
date 转换后的日期
---------------------------------- ----------------------------
1900-1-1 00:00:00.000 1900-01-01 00:00:00.000
1900-1-1 00:00:00.001 1900-01-01 00:00:00.000
1900-1-1 00:00:00.009 1900-01-01 00:00:00.010
1900-1-1 00:00:00.002 1900-01-01 00:00:00.003
1900-1-1 00:00:00.003 1900-01-01 00:00:00.003
1900-1-1 00:00:00.004 1900-01-01 00:00:00.003
1900-1-1 00:00:00.005 1900-01-01 00:00:00.007
1900-1-1 00:00:00.006 1900-01-01 00:00:00.007
1900-1-1 00:00:00.007 1900-01-01 00:00:00.007
1900-1-1 00:00:00.008 1900-01-01 00:00:00.007
--*/

datetime的存储长度为8字节，日期和时间各用4个字节存储，第一个4字节存储自1900年1月1日之前或之后的天数（以1900年1月1日为分界点，在1900年1月1日之前的日期的天数小于0，在1900年1月1日之后的日期的天数大于0）。另外一个4字节存储以午夜（00:00:00.000）后毫秒数所代表的每天的时间。
例如，下面的代码演示了datetime变量中，仅包含单纯的日期和单纯的时间时，日期存储的十六进制存储表示结果。
DECLARE @dt datetime

--单纯的日期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
--结果: 0x0000000100000000

--单纯的时间
SET @dt='00:00:01'
SELECT CAST(@dt as binary(8))
--结果: 0x000000000000012C

2．smalldatetime
smalldatetime类型处理从1900年1月1日～2079年6月6 日的日期和时间数据，精确到分钟。29.998秒或更低的smalldatetime值向下舍入为最接近的分钟，29.999秒或更高的smalldatetime值向上舍入为最接近的分钟。
smalldatetime的存储长度为4字节，第一个2字节存储自1900年1月1日之后的天数。另外一个2字节存储午夜（00:00:00.000）后的分钟数。
例如，下面的代码演示了smalldatetime变量中，仅包含单纯的日期和单纯的时间时，日期存储的十六进制存储表示结果。
DECLARE @dt smalldatetime

--单纯的日期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(4))
--结果: 0x00010000

--单纯的时间
SET @dt='00:10'
SELECT CAST(@dt as binary(4))
--结果: 0x0000000A

1.2 日期处理函数
日期由年、月、日、时等多个部分组成，它的处理相对复杂，因此，SQL Server提供了大量的日期处理函数，用以完成各种日期数据的处理。掌握好这些函数，对完成数据库的各种日期处理非常必要，本节将介绍几个常用的日期处理函数。

1.2.1 日期增减函数
日期增减函数可以对日期指定部分的值进行增减，并返回处理后的日期值，SQL Server提供的日期增减函数为DATEADD。

DATEADD的具体语法如下：
DATEADD ( datepart , number, date )
其中包括以下参数。
¡ datepart：是规定应向日期的哪一部分返回新值的参数。表2-1列出了SQL Server支持的日期部分、缩写及含义。
DATEADD、DATEDIFF支持的日期部分、缩写及含义
表2.1

日期部分	缩 写	含 义
Year	yy , yyyy	年份
Quarter	qq , q	季度
Month	mm , m	月份
Dayofyear	dy,y	日
Day	dd , d
Week	wk , ww	星期
Hour	Hh	小时
Minute	mi , n	分钟
Second	ss , s	秒
Millisecond	Ms	毫秒

¡ number：是用来增加datepart的值。正数表示增加，负数表示减少，如果指定的是非整数值，则忽略此值的小数部分，不做四舍五入处理。例如，DATEADD（Day,1.7,date），表示date增加1天。
¡ date：是返回datetime或smalldatetime值或日期格式字符串的表达式。
如果date是smalldatetime，则返回smalldatetime，否则返回datetime。date为smalldatetime，Datepart为Second（ss,s）或Millisecond（ms）时，返回值将根据日期增减的结果调整到分钟；date为datetime，Datepart为Millisecond（ms）时，返回值将根据日期增减的结果调整为百分之三秒。调整规则可以参考2.1节的相关说明。
date允许直接与number进行增减计算，即对于DATEADD（Day,number,date），等同于date+number。

1.2.2 日期信息获取函数
日期信息获取函数用于获取日期指定部分的相关信息，常用的日期信息获取函数如表2-2所示。
常用的日期信息获取函数
表2.2

功能说明	语 法	参数及返回值数据类型说明
返回代表指定日期的指定日期部分的字符串	DATENAME(datepart,date)	datepart是指定应返回的日期部分的参数，其定义如表2-3所示。date是返回datetime或smalldatetime值或日期格式字符串的表达式。DATENAME函数返回nvarchar，DATEPART函数返回int
返回代表指定日期的指定日期部分的整数	DATEPART(datepart,date)
返回表示指定日期中的年份的整数	YEAR(date)	返回int
返回表示指定日期中的月份的整数	MONTH(date)	返回int
返回表示指定日期中的天的整数	DAY(date)	返回int

表2-3 DATENAME、DATEPART支持的日期部分、缩写及含义

日期部分	缩 写	含 义
Year	yy , yyyy	年份
Quarter	qq , q	季度
Month	mm , m	月份
Dayofyear	dy , y	日
Day	dd , d
Week	wk , ww	自年初开始的第几个星期
Weekday	Dw	星期几（例如星期一、星期二）
Hour	Hh	小时
Minute	mi , n	分钟
Second	ss , s	秒。date为smalldatetime时，始终返回0
Millisecond	Ms	毫秒。date为smalldatetime时，始终返回0，为datetime时，返回百份之三秒

DATEPART（Week,date）返回的星期计算方式，是按照星期日为一周的第一天，这点与中国人的日期处理习惯不同，在使用时要注意这一点。DATENAME函数返回指定日期的指定日期部分的字符串，其返回的具体字符串值，与SET DATEFIRST及SET DATELANGUAGE选项的设置有关。使用DATEPART（Weekday,date）时，其返回的值与SET DATEFIRST选项的设置有关，具体的将在2.3节中说明。

1.2.3 日期差值计算函数
日期差值计算函数用于计算两个给定日期指定部分的边界数，SQL Server提供的日期差值计算函数为DATEDIFF。
DATEDIFF的具体语法如下：
DATEDIFF ( datepart , startdate , enddate )
其中包括以下参数。
¡ datepart：规定了应在日期的哪一部分计算差额，其定义如表2-1所示。
¡ startdate：规定了计算的开始日期。
¡ enddate：规定了计算的终止日期。

返回类型：integer

计算的开始日期和终止日期，可以是日期或日期格式的字符串。计算的方法是从enddate减去startdate。如果startdate比enddate晚，返回负值。当结果超出整数值范围，DATEDIFF就产生错误。对于毫秒，最大数是24天20小时31分钟23.647秒。对于秒，最大数是68年。
计算跨分钟、秒和毫秒这些边界的方法，使得DATEDIFF给出的结果在全部数据类型中是一致的。结果是带正负号的整数值，其等于跨第一个和第二个日期间的datepart边界数。例如，在2005年1月4日和2005年2月11日之间的月份数是1。

1.2.4 其他日期处理相关函数
其他常用的日期处理相关函数包括以下几个。

1．GETDATE
GETDATE按照datetime值返回当前系统日期和时间。
GETDATE的语法如下：
GETDATE()
返回类型：datetime

2．ISDATE
ISDATE确定输入的表达式是否有效日期。
在输入日期表达式时，日期都是以日期格式的字符串提供的，由于不同的区域有不同的日期格式，所以并不能保证输入的日期表达式能够被SQL Server识别，这种情况下，就需要用ISDATE来判断日期表达式能否正确地被SQL Server识别了。
ISDATE的语法如下：
ISDATE(expression)
返回类型：int

3．CONVERT
CONVERT将某种数据类型的表达式显式转换为另一种数据类型。
严格来说，CONVERT不属于日期处理函数，只是它被经常用于日期处理中，所以这里把它列入了其他日期处理函数，下面是CONVERT的用法描述（只重点说明在日期处理中的应用）。
CONVERT的具体语法如下：
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
其中包括以下参数。
¡ expression：是要转换数据类型的有效SQL Server表达式。
¡ data_type：是expression转换后的数据类型，length是对于有精度定义需要的data_type的精度定义，对于没有精度定义需要的data_type，该参数可以省略。
¡ style：定义数据类型转换时的格式，对于日期类型的转换，它的定义如表2-4所示。
表2.4
style在日期转换中的说明

不带世纪数位	带世纪数位	标 准	输入/输出
—	0或100	默认值	mon dd yyyy hh:miAM(或 PM)
1	101	美国	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	英国/法国	dd/mm/yy
4	104	德国	dd.mm.yy
5	105	意大利	dd-mm-yy
6	106	—	dd mon yy
7	107	—	mon dd, yy
8	108	—	hh:mm:ss
—	9或109	默认值+毫秒	mon dd yyyy hh:mi:ss:mmmAM(或PM)
10	110	美国	mm-dd-yy
11	111	日本	yy/mm/dd
12	112	ISO	yymmdd
—	13或113	欧洲默认值+毫秒	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	—	hh:mi:ss:mmm(24h)
—	20或120	ODBC规范	yyyy-mm-dd hh:mm:ss[.fff]
—	21或121	ODBC规范（带毫秒）	yyyy-mm-dd hh:mm:ss[.fff]
—	126	ISO8601	yyyy-mm-ddThh:mm:ss.mmm
—	130	Hijri	dd mon yyyy hh:mi:ss:mmmAM
—	131	Hijri	dd/mm/yy hh:mi:ss:mmmAM

说明：

① 输入/输出：“输入”表示从字符串转换为日期时字符串的日期格式，“输出”指从日期转换为字符串时的日期字符串格式。
② Hijri：是具有几种变化形式的日历系统，SQL Server使用其中的科威特算法。

当从smalldatetime转换为字符数据时，由于smalldatetimer只保存到分钟的数据，因此，对于包含秒或毫秒的样式，将在秒或毫秒的位置上显示零。当从datetime或smalldatetime值进行转换时，可以通过使用适当的char或varchar数据类型长度来截断不需要的日期部分。

注意：
在SQL Server中，由于直接提供的日期均是以日期格式的字符串提供，所以在使用CONVERT进行日期格式转换时，要先把日期格式的字符串转换为日期型，然后才能利用CONVERT进行日期格式转换，否则就变成字符串转换为字符串，此时的style选项是无效的。
返回类型：由参数data_type确定。

下面是利用CONVERT进行日期转换的简单示例：
/*== 字符转换为日期时,Style的使用 ==*/
--1. Style=101时,表示日期字符串为:mm/dd/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',101)
--结果:2003-11-01 00:00:00.000

--2. Style=101时,表示日期字符串为:dd/mm/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',103)
--结果:2003-01-11 00:00:00.000

/*== 日期转换为字符串 ==*/
DECLARE @dt datetime
SET @dt='2003-1-11'
--1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式

SELECT CONVERT(varchar,@dt,101)
--结果:01/11/2003
--2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式
SELECT CONVERT(varchar,@dt,103)
--结果:11/01/2003

/*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==*/
SELECT CONVERT(varchar,'2003-1-11',101)
--结果:2003-1-11

Tag标签: SQL Server