T-SQl 日期函数（转）

 

      日期函数

这些函数可以操作DateTime与SmallDateTime类型的值。有些函数可用于解析日期值的日期与时间部分，有些函数可用于比较、操纵日期/时间值。DateTime与SmallDateTime数据类型的区别如表6-10中所示。

表  6-10

数 据 类 型

存储空间需求

日 期 范 围

datetime

8字节

1/1/1753—12/31/9999

smalldatetime

4字节

1/1/1900—6/6/2079

 

1  DATEADD()函数

DATEADD()函数用于在日期/时间值上加上日期单位间隔。比如，要得到2007年4月29日起90天后的日期，可以使用下列语句：

SELECT DATEADD(Day, 90, '4-29-2007')

结果如下:

2007-07-28  00:00:00.000

可以把表6-11的值作为时间间隔参数传递给DATEADD()函数。

表  6-11

时 间 间 隔

时间间隔参数值

年

Year，yyyy，yy

季度

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

 

在下面列出的例子中，我们使用和上一个例子一样的日期，并且在这些例子中还包含了时间数据。每个操作的结果将显示在查询的下一行中。

18年后：

SELECT DATEADD(Year, 18, '4-29-1988 10:30 AM')

2006-04-29 10:30:00.000

18年前：

SELECT DATEADD(yy, -18, '4-29-1988 10:30 AM')

1970-04-29 10:30:00.000

9000秒后：

SELECT DATEADD(Second, 9000, '4-29-1988 10:30 AM')

1988-04-29 13:00:00.000

9000000毫秒前:

SELECT DATEADD(ms, -9000000, '4-29-1988 10:30 AM')

1988-04-29 08:00:00.000

可以将CONVERT()函数和DATEADD()函数组合在一起，来对19**9月8日9个月前的日期值进行格式化。

SELECT CONVERT(varchar(20), DATEADD(m, -9, '9-8-1989'), 101)

12/08/1988

这将返回一个可变长度的字符值，比前面例子结果中的默认日期更易容易理解。这是一个函数嵌套调用，DATEADD()函数的返回值(一个DateTime类型的值)被作为值参数传递给CONVERT()函数。

2  DATEDIFF()函数

DATEADD()和DATEDIFF()函数可以看作一对表兄弟，有点像乘法与除法。在等式的两端有4个元素：起始日期、时间间隔、差值和最终日期。如果已知其中的三个值，就可以求出第4个值。如果在DATEADD()函数中使用起始日期、一个整型值和一个时间间隔，就可返回与起始日期相关的最终日期值。如果提供了起始日期、时间间隔和最终日期，DATEDIFF()函数就可以返回差值。

为了说明这一点，我们选择任意两个日期与一个时间间隔作为参数。这个函数将以所提供的时间间隔为单位返回两个日期之间的差值。要知道19**9月8日和1991年10月17日之间差了几个月，可编写如下查询代码：

SELECT DATEDIFF(month, '9-8-1989', '10-17-1991')

结果是25个月。如果以日期为单位呢?

SELECT DATEDIFF(day, '9-8-1989', '10-17-1991')

结果是769天。

1996年7月2日和1997年8月4日之间差几个星期?

SELECT DATEDIFF(week, '7-2-1996', '8-4-1997')

57星期。甚至可以算出自己的年龄是多少秒：

DECLARE @MyBirthDate datetime

SET @MyBirthDate = '7-16-1962'

SELECT DATEDIFF(ss, @MyBirthDate, GETDATE())

结果显示有些人已经活了15亿秒了!

可以将列名作为参数，把这个函数用在查询中。首先建立一个简单的表，其中包含一些人的姓名和生日，这里使用第5章中的简单表结构：

--SQL Server 2005/2008 syntax:

USE AdventureWorks2008

GO

CREATE TABLE dbo.SlateGravelEmployee

(LastName varchar(25) NULL

,FirstName varchar(25) NULL

,Position varchar(25) NULL

,BirthDate datetime NULL);

INSERT SlateGravelEmployee

VALUES

('Flintstone', 'Fred', 'Bronto Driver', '12-31-1960')

INSERT SlateGravelEmployee

VALUES

('Rubble', 'Barney', 'Accountant', '02-14-1964')

INSERT SlateGravelEmployee

VALUES

('Turleyrock', 'Paul', 'Developer', '03-24-1967')

INSERT SlateGravelEmployee

VALUES

('Petriwood', 'Dan', 'DBA', '07-16-1962')

INSERT SlateGravelEmployee

VALUES

('Johnstone', 'Randy', 'System Administrator', '03-14-1961')

INSERT SlateGravelEmployee

VALUES

('Witherstone', 'Mark', 'Vice President', '09-27-1967')

--SQL Server 2008 syntax:

USE AdventureWorks2008

GO

CREATE TABLE dbo.SlateGravelEmployee

(LastName varchar(25) NULL

,FirstName varchar(25) NULL

,Position varchar(25) NULL);

,BirthDate datetime NULL);

INSERT SlateGravelEmployee

VALUES

 ('Flintstone', 'Fred', 'Bronto Driver', '12-31-1960')

,('Rubble', 'Barney', 'Accountant', '02-14-1964')

,('Turleyrock', 'Paul', 'Developer', '03-24-1967')

,('Petriwood', 'Dan', 'DBA', '07-16-1962')

,('Johnstone', 'Randy', 'System Administrator', '03-14-1961')

,('Witherstone', 'Mark', 'Vice President', '09-27-1967')

下面的查询从表中返回姓名和生日。DATEDIFF()函数用于从包含datetime数据的列中提取信息，以确定每个Slate Gravel员工的大致年龄：

SELECT LastName

      ,FirstName

      ,BirthDate

      ,DATEDIFF(YEAR, BirthDate, GETDATE()) AS ApproximateAge

FROM SlateGravelEmployee

ORDER BY LastName

图**显示了结果。

图  **

初看起来结果是对的，但存在的问题是年龄值没有精确到日。比如，根据表中的数据，Fred的生日是12月31日，他今年将庆祝第48个生日(这个查询在2008年7月运行)。如果依据上述计算结果来确定他的年龄何时变化，就应在一月份的某天给他发生日卡片，这比实际日期提前了11个月。

除非用更小的时间单位来计算这些日期的差，否则结果只在雇员实际生日的一年以内是精确的。以下例子将用差值除以一年(包括闰年)的天数，并将结果值转换为int类型，进行取整运算，而不是四舍五入。

SELECT LastName

      ,FirstName

      ,BirthDate

      ,DATEDIFF(YEAR, BirthDate, GETDATE()) AS ApproximateAge

      ,CONVERT(int, DATEDIFF(day, BirthDate, GETDATE())/365.25) AS Age

FROM SlateGravelEmployee

ORDER BY LastName

比较图6-5的结果和上一个例子的结果，看看有什么不同。

图  6-5

可以看到，Fred是47岁，其他雇员的年龄也精确到了天。表中的BirthDate列存储雇员的生日，并以午夜(00:00:00AM)为界，这是一天中的第一秒。GETDATE()函数返回当前的时间与日期。当前两个日期相差约8小时(写这段文字时是上午8点)。如果希望这个计算更精确，就需要在当前日期的午夜把GETDATE()函数的结果转换为datetime类型。

3  DATEPART()与DATENAME()函数

这两个函数用于返回datetime或者shortdatetime值的日期部分。DATEPART()函数返回一个整型值；DATENAMEO函数返回一个包含描述性文字的字符串。比如，将日期4-29-1988传递给DATEPART()函数，如指定返回月份值，则返回数字4：

SELECT DATEPART(month, '4-29-1988')

而使用相同的参数，DATENAME()函数返回April：

SELECT DATENAME(month, '4-29-1988')

这两个函数都接收和DATEADD()函数一样的时间间隔参数常量。

4  GETDATE()与GETUTCDATE()函数

这两个函数都用于返回datetime类型的当前日期与时间。GETUTCDATE()函数使用服务器上的时区设置来求出UTC时间，这和格林威治标准时间或飞行员所说的“祖鲁时”(Zulu Time)是一样的。两个函数都能精确到3.33毫秒。

SELECT GETDATE()

SELECT GETUTCDATE()

执行这两个函数，都将返回未经格式化的结果，见图6-6。

图  6-6

我在太平洋时区，和UTC时间相差7个小时，和标准时间相差8个小时。可以使用如下DATEDIFF()函数来验证这个时间差值：

SELECT DATEDIFF(hour, GETDATE(), GETUTCDATE())

5  SYSDATETIME()和SYSUTCDATETIME()函数

这两个SQL Server 2008函数等价于GETDATE()和GETUTCDATE()函数，但不是返回datetime数据类型的结果，而是返回SQL Server 2008新的datetime2数据类型的结果，该数据类型可以精确到100纳秒，当然这取决于服务器安装的硬件。

SELECT SYSDATETIME()

SELECT SYSUTCDATETIME()

6  DAY()、MONTH()和YEAR()函数

这三个函数分别返回以整数表示的datetime或者smalldatetime类型值的日、月、年。它们的用途很广泛，如可以创建独特的个性化日期格式。假设需要创建一个自定义的日期值作为字符串，通过将这三个函数的输出结果转换成字符类型，然后进行连接操作，就可以对输出结果以任何形式进行组合了：

SELECT 'Year: ' + CONVERT(varchar(4), YEAR(GETDATE()))

   + ', Month: ' + CONVERT(varchar(2), MONTH(GETDATE()))

   + ', Day: ' + CONVERT(varchar(2), DAY(GETDATE()))

这个脚本生成下列结果：

Year:2008, Month:2, Day:20