sql server中的日期函数

DATEADD   在向指定日期加上一段时间的基础上,返回新的 datetime 值。

日期处理datetime和date之间的相互转换

语法           DATEADD ( datepart , number, date )

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

参数           

日期类型概述

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

(1) datepart:是规定应向日期的哪一部分返回新值的参数。

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

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.000    1900-01-01 00:00:00.010

–*/

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

下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。
日期部分 缩写
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

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

(2)number:是用来增加 datepart
的值。如果指定一个不是整数的值,则将废弃此值的小数部分。

日期处理函数

日期由年、月、日、时等多个部分组成,它的处理相对复杂,因此,SQL Server提供了大量的日期处理函数,用以完成各种日期数据的处理。掌握好这些函数,对完成数据库的各种日期处理非常必要,本节将介绍几个常用的日期处理函数。期增减函数可以对日期指定部分的值进行增减,并返回处理后的日期值,SQL Server提供的日期增减函数为DATEADD。

DATEADD的具体语法如下:DATEADD ( datepart , number, date )

其中包括以下参数。

¡ 
datepart:是规定应向日期的哪一部分返回新值的参数。表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

小时

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。

    例如,如果为 datepart 指定 day,为 number 指定 1.75,则 date 将增加
1。

 日期信息获取函数

日期信息获取函数用于获取日期指定部分的相关信息,常用的日期信息获取函数如表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

                  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节中说明。

(3)date:是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。

  日期差值计算函数

日期差值计算函数用于计算两个给定日期指定部分的边界数,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。

    有关指定日期的更多信息,请参见 datetime 和 smalldatetime。

 其他日期处理相关函数

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

如果您只指定年份的最后两位数字,则小于或等于”两位数年份截止期”配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如,如果
two digit year cutoff 为 2049(默认),则 49 被解释为 2049,2050
被解释为 1950。为避免模糊,请使用四位数的年份。

1.GETDATE

GETDATE按照datetime值返回当前系统日期和时间。

GETDATE的语法如下:

GETDATE()

返回类型:datetime