Sealyu

日期是數(shù)據(jù)處理中經(jīng)常使用到的信息之一。生日、數(shù)據(jù)處理時(shí)間、計(jì)劃的預(yù)計(jì)完成時(shí)間，按年、季、月的統(tǒng) 計(jì)，這些都屬于日期處理的范疇。由于日期中包含了年、季、月、日等眾多信息，不同的國(guó)家對(duì)日期格式、日期文字描述及星期有不同的規(guī)定，因此產(chǎn)生了日期處理 的復(fù)雜性。本章主要討論在SQL Server數(shù)據(jù)庫中對(duì)日期的各種處理方法。

日期類型概述

SQL Server中的日期類型包括datetime和smalldatetime，僅能處理可以識(shí)別為1753年～9999年間的日期的值，沒有單獨(dú)的日期型或時(shí)間型。

1．datetime

datetime類型處理從1753年1月1日～9999年12月31日的日期和時(shí)間數(shù)據(jù)，精確度為百分之三秒。即：對(duì)于0.000～0.001、0.009的日期值，調(diào)整為0.000；對(duì)于0.002～0.004的日期值，調(diào)整為0.003；對(duì)于0.005～0.008的日期值，調(diào)整為0.007。

例如，下面的代碼在輸入時(shí)，其時(shí)間精確度為百分之一秒，但經(jīng)數(shù)據(jù)庫保存后再顯示出來，其結(jié)果就已經(jīng)做了處理。

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.009'

SELECT date,轉(zhuǎn)換后的日期=CAST(date as datetime) FROM @t

/*--結(jié)果

date                     轉(zhuǎn)換后的日期

---------------------------------- ----------------------------

1900-1-1 00:00:00.000    1900-01-01 00:00:00.000
...
1900-1-1 00:00:00.000    1900-01-01 00:00:00.010

--*/

datetime的存儲(chǔ)長(zhǎng)度為8字節(jié)，日期和時(shí)間各用4個(gè)字節(jié)存儲(chǔ)，第一個(gè)4字節(jié)存儲(chǔ)自1900年1月1日之前或之后的天數(shù)（以1900年1月1日為分界點(diǎn)，在1900年1月1日之前的日期的天數(shù)小于0，在1900年1月1日之后的日期的天數(shù)大于0）。另外一個(gè)4字節(jié)存儲(chǔ)以午夜（00:00:00.000）后毫秒數(shù)所代表的每天的時(shí)間。

例如，下面的代碼演示了datetime變量中，僅包含單純的日期和單純的時(shí)間時(shí)，日期存儲(chǔ)的十六進(jìn)制存儲(chǔ)表示結(jié)果。

DECLARE @dt datetime

--單純的日期

SET @dt='1900-1-2'

SELECT CAST(@dt as binary(8))

--結(jié)果: 0x0000000100000000

--單純的時(shí)間

SET @dt='00:00:01'

SELECT CAST(@dt as binary(8))

--結(jié)果: 0x000000000000012C

2．smalldatetime

smalldatetime類型處理從1900年1月1日～2079年6月6 日的日期和時(shí)間數(shù)據(jù)，精確到分鐘。29.998秒或更低的smalldatetime值向下舍入為最接近的分鐘，29.999秒或更高的smalldatetime值向上舍入為最接近的分鐘。

smalldatetime的存儲(chǔ)長(zhǎng)度為4字節(jié)，第一個(gè)2字節(jié)存儲(chǔ)自1900年1月1日之后的天數(shù)。另外一個(gè)2字節(jié)存儲(chǔ)午夜（00:00:00.000）后的分鐘數(shù)。

例如，下面的代碼演示了smalldatetime變量中，僅包含單純的日期和單純的時(shí)間時(shí)，日期存儲(chǔ)的十六進(jìn)制存儲(chǔ)表示結(jié)果。

DECLARE @dt smalldatetime

--單純的日期

SET @dt='1900-1-2'

SELECT CAST(@dt as binary(4))

--結(jié)果: 0x00010000

--單純的時(shí)間

SET @dt='00:10'

SELECT CAST(@dt as binary(4))
--結(jié)果: 0x0000000A

日期處理函數(shù)

日期由年、月、日、時(shí)等多個(gè)部分組成，它的處理相對(duì)復(fù)雜，因此，SQL Server提供了大量的日期處理函數(shù)，用以完成各種日期數(shù)據(jù)的處理。掌握好這些函數(shù)，對(duì)完成數(shù)據(jù)庫的各種日期處理非常必要，本節(jié)將介紹幾個(gè)常用的日期處理函數(shù)。期增減函數(shù)可以對(duì)日期指定部分的值進(jìn)行增減，并返回處理后的日期值，SQL Server提供的日期增減函數(shù)為DATEADD。

DATEADD的具體語法如下：DATEADD ( datepart , number, date )

其中包括以下參數(shù)。

¡ datepart：是規(guī)定應(yīng)向日期的哪一部分返回新值的參數(shù)。表2-1列出了SQL Server支持的日期部分、縮寫及含義。

                    DATEADD、DATEDIFF支持的日期部分、縮寫及含義

日期部分	縮    寫	含    義
Year	yy , yyyy	年份
Quarter	qq , q	季度
Month	mm , m	月份
Dayofyear	dy,y	日
Day	dd , d
Week	wk , ww	星期
Hour	Hh	小時(shí)
Minute	mi , n	分鐘
Second	ss , s	秒
Millisecond	Ms	毫秒

¡ number：是用來增加datepart的值。正數(shù)表示增加，負(fù)數(shù)表示減少，如果指定的是非整數(shù)值，則忽略此值的小數(shù)部分，不做四舍五入處理。例如，DATEADD（Day,1.7,date），表示date增加1天。

¡ date：是返回datetime或smalldatetime值或日期格式字符串的表達(dá)式。

如果date是smalldatetime，則返回smalldatetime，否則返回datetime。date為smalldatetime，Datepart為Second（ss,s）或Millisecond（ms）時(shí)，返回值將根據(jù)日期增減的結(jié)果調(diào)整到分鐘；date為datetime，Datepart為Millisecond（ms）時(shí)，返回值將根據(jù)日期增減的結(jié)果調(diào)整為百分之三秒。調(diào)整規(guī)則可以參考2.1節(jié)的相關(guān)說明。

date允許直接與number進(jìn)行增減計(jì)算，即對(duì)于DATEADD（Day,number,date），等同于date+number。

 日期信息獲取函數(shù)

日期信息獲取函數(shù)用于獲取日期指定部分的相關(guān)信息，常用的日期信息獲取函數(shù)如表2-2所示。

                                      常用的日期信息獲取函數(shù)

功能說明	語    法	參數(shù)及返回值數(shù)據(jù)類型說明
返回代表指定日期的指定日期部分的字符串	DATENAME(datepart,date)	datepart是指定應(yīng)返回的日期部分的參數(shù)，其定義如表2-3所示。date是返回datetime或smalldatetime值或日期格式字符串的表達(dá)式。DATENAME函數(shù)返回nvarchar，DATEPART函數(shù)返回int
返回代表指定日期的指定日期部分的整數(shù)	DATEPART(datepart,date)
返回表示指定日期中的年份的整數(shù)	YEAR(date)	返回int
返回表示指定日期中的月份的整數(shù)	MONTH(date)	返回int
返回表示指定日期中的天的整數(shù)	DAY(date)	返回int

                  DATENAME、DATEPART支持的日期部分、縮寫及含義

日期部分	縮    寫	含    義
Year	yy , yyyy	年份
Quarter	qq , q	季度
Month	mm , m	月份
Dayofyear	dy , y	日
Day	dd , d
Week	wk , ww	自年初開始的第幾個(gè)星期
Weekday	Dw	星期幾（例如星期一、星期二）
Hour	Hh	小時(shí)
Minute	mi , n	分鐘
Second	ss , s	秒。date為smalldatetime時(shí)，始終返回0
Millisecond	Ms	毫秒。date為smalldatetime時(shí)，始終返回0，為datetime時(shí)，返回百份之三秒

DATEPART（Week,date）返回的星期計(jì)算方式，是按照星期日為一周的第一天，這點(diǎn)與中國(guó)人的日期處理習(xí)慣不同，在使用時(shí)要注意這一點(diǎn)。DATENAME函數(shù)返回指定日期的指定日期部分的字符串，其返回的具體字符串值，與SET DATEFIRST及SET DATELANGUAGE選項(xiàng)的設(shè)置有關(guān)。使用DATEPART（Weekday,date）時(shí)，其返回的值與SET DATEFIRST選項(xiàng)的設(shè)置有關(guān)，具體的將在2.3節(jié)中說明。

 日期差值計(jì)算函數(shù)

日期差值計(jì)算函數(shù)用于計(jì)算兩個(gè)給定日期指定部分的邊界數(shù)，SQL Server提供的日期差值計(jì)算函數(shù)為DATEDIFF。

DATEDIFF的具體語法如下：

DATEDIFF ( datepart , startdate , enddate )

其中包括以下參數(shù)。

¡ datepart：規(guī)定了應(yīng)在日期的哪一部分計(jì)算差額，其定義如表2-1所示。

¡ startdate：規(guī)定了計(jì)算的開始日期。

¡ enddate：規(guī)定了計(jì)算的終止日期。

返回類型：integer

計(jì)算的開始日期和終止日期，可以是日期或日期格式的字符串。計(jì)算的方法是從enddate減去startdate。如果startdate比enddate晚，返回負(fù)值。當(dāng)結(jié)果超出整數(shù)值范圍，DATEDIFF就產(chǎn)生錯(cuò)誤。對(duì)于毫秒，最大數(shù)是24天20小時(shí)31分鐘23.647秒。對(duì)于秒，最大數(shù)是68年。

計(jì)算跨分鐘、秒和毫秒這些邊界的方法，使得DATEDIFF給出的結(jié)果在全部數(shù)據(jù)類型中是一致的。結(jié)果是帶正負(fù)號(hào)的整數(shù)值，其等于跨第一個(gè)和第二個(gè)日期間的datepart邊界數(shù)。例如，在2005年1月4日和2005年2月11日之間的月份數(shù)是1。

 其他日期處理相關(guān)函數(shù)

其他常用的日期處理相關(guān)函數(shù)包括以下幾個(gè)。

1．GETDATE

GETDATE按照datetime值返回當(dāng)前系統(tǒng)日期和時(shí)間。

GETDATE的語法如下：

GETDATE()

返回類型：datetime

2．ISDATE

ISDATE確定輸入的表達(dá)式是否有效日期。

在輸入日期表達(dá)式時(shí)，日期都是以日期格式的字符串提供的，由于不同的區(qū)域有不同的日期格式，所以并不能保證輸入的日期表達(dá)式能夠被SQL Server識(shí)別，這種情況下，就需要用ISDATE來判斷日期表達(dá)式能否正確地被SQL Server識(shí)別了。

ISDATE的語法如下：

ISDATE(expression)

返回類型：int

3．CONVERT

CONVERT將某種數(shù)據(jù)類型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型。

嚴(yán)格來說，CONVERT不屬于日期處理函數(shù)，只是它被經(jīng)常用于日期處理中，所以這里把它列入了其他日期處理函數(shù)，下面是CONVERT的用法描述（只重點(diǎn)說明在日期處理中的應(yīng)用）。

CONVERT的具體語法如下：

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

其中包括以下參數(shù)。

¡ expression：是要轉(zhuǎn)換數(shù)據(jù)類型的有效SQL Server表達(dá)式。

¡ data_type：是expression轉(zhuǎn)換后的數(shù)據(jù)類型，length是對(duì)于有精度定義需要的data_type的精度定義，對(duì)于沒有精度定義需要的data_type，該參數(shù)可以省略。

¡ style：定義數(shù)據(jù)類型轉(zhuǎn)換時(shí)的格式，對(duì)于日期類型的轉(zhuǎn)換，它的定義如表2-4所示。

表2-4                                      style在日期轉(zhuǎn)換中的說明

不帶世紀(jì)數(shù)位	帶世紀(jì)數(shù)位	標(biāo)    準(zhǔn)	輸入/輸出
—	0或100	默認(rèn)值	mon dd yyyy hh:miAM(或 PM)
1	101	美國(guó)	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	英國(guó)/法國(guó)	dd/mm/yy
4	104	德國(guó)	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	默認(rèn)值+毫秒	mon dd yyyy hh:mi:ss:mmmAM(或PM)
10	110	美國(guó)	mm-dd-yy
11	111	日本	yy/mm/dd
12	112	ISO	yymmdd
—	13或113	歐洲默認(rèn)值+毫秒	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	—	hh:mi:ss:mmm(24h)
—	20或120	ODBC規(guī)范	yyyy-mm-dd hh:mm:ss[.fff]
—	21或121	ODBC規(guī)范（帶毫秒）	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

當(dāng)從smalldatetime轉(zhuǎn)換為字符數(shù)據(jù)時(shí)，由于smalldatetimer只保存到分鐘的數(shù)據(jù)，因此，對(duì)于包含秒或毫秒的樣式，將在秒或毫秒的位置上顯示零。當(dāng)從datetime或smalldatetime值進(jìn)行轉(zhuǎn)換時(shí)，可以通過使用適當(dāng)?shù)腸har或varchar數(shù)據(jù)類型長(zhǎng)度來截?cái)嗖恍枰娜掌诓糠帧?/font>

注意：

在SQL Server中，由于直接提供的日期均是以日期格式的字符串提供，所以在使用CONVERT進(jìn)行日期格式轉(zhuǎn)換時(shí)，要先把日期格式的字符串轉(zhuǎn)換為日期型，然后才能利用CONVERT進(jìn)行日期格式轉(zhuǎn)換，否則就變成字符串轉(zhuǎn)換為字符串，此時(shí)的style選項(xiàng)是無效的。

返回類型：由參數(shù)data_type確定。

下面是利用CONVERT進(jìn)行日期轉(zhuǎn)換的簡(jiǎn)單示例：

/*== 字符轉(zhuǎn)換為日期時(shí),Style的使用 ==*/

--1. Style=101時(shí),表示日期字符串為:mm/dd/yyyy格式

SELECT CONVERT(datetime,'11/1/2003',101)

--結(jié)果:2003-11-01 00:00:00.000

--2. Style=101時(shí),表示日期字符串為:dd/mm/yyyy格式

SELECT CONVERT(datetime,'11/1/2003',103)

--結(jié)果:2003-01-11 00:00:00.000 

/*== 日期轉(zhuǎn)換為字符串 ==*/

DECLARE @dt datetime

SET @dt='2003-1-11'

--1. Style=101時(shí),表示將日期轉(zhuǎn)換為:mm/dd/yyyy 格式

SELECT CONVERT(varchar,@dt,101)

--結(jié)果:01/11/2003

--2. Style=103時(shí),表示將日期轉(zhuǎn)換為:dd/mm/yyyy 格式

SELECT CONVERT(varchar,@dt,103)

--結(jié)果:11/01/2003

 /*== 這是很多人經(jīng)常犯的錯(cuò)誤,對(duì)非日期型轉(zhuǎn)換使用日期的style樣式 ==*/

SELECT CONVERT(varchar,'2003-1-11',101)

--結(jié)果:2003-1-11