使用DATEADD和DATEDIFF来计算SQL Server的DATETIME值

转载 2015年07月10日 16:29:01


在SQL Server数据库中,DATETIME和SMALLDATETIME值是以整数存储的。然而,与整数不同的是,它们不能直接地进行数学运算。尽管如此,有时候还是需要在日期/时间值中添加或减去一个时间间隔。比如,你可能想在一值上加一些月数或天数,或者甚至可能是小时数。你甚至可能想比较两个日期/时间值以便确定它们之间的时间间隔,如相差的天数或年数。为了简化这些类型的计算,Transact-SQL支持两个重要的日期/时间方法:DATEADD和DATEDIFF。

 

在关于DATETIME值这一系列文章的第四部分,我阐述了如何使用这两个方法并举例说明它们是如何工作的。为了演示这些方法,我使用了下面的Transact-SQL代码在AdventureWorks示例数据库中创建了一个Sales.Orders表:

 


USE AdventureWorks 
GO 
IF EXISTS (SELECT table_name  
FROM information_schema.tables  
WHERE table_schema = 'Sales' 
AND table_name = 'Orders') 
DROP TABLE Sales.Orders 
GO 
CREATE TABLE Sales.Orders 
( 
OrderID INT NOT NULL, 
OrderDate DATETIME NOT NULL, 
DelivDate DATETIME NOT NULL 
 
GO 
INSERT INTO Sales.Orders 
VALUES(1001, GETDATE(), '2008-09-08 18:27:10.750')

 

表的定义包含了OrderDate和DelivDate字段,两者都是DATETIME数据类型。在我创建了表之后,我在表中插入了一行用于测试DATEADD和DATEDIFF方法的数据。

 

使用DATEADD方法

 

在一些情况下,你可能想添加一个时间间隔到DATETIME或SMADDDATETIME值中——或者减去一个时间间隔。比如,你可能需要在一个指定的日期中增加或减去一个月。你可以使用DATEADD方法来执行这个计算。这个方法运用了下面的语法:

 


DATEADD(<date/time_part>, <number>, <date>)

 

<date/time_part>占位符指的是日期/时间值中增加或减少的增量/余差(如日或月)。下表列出了可以使用的日期/时间部分,以及代表这些部分的缩写:

 

Date/time part

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

day of year

dy, y

day

dd, d

week

wk, ww

weekday

dw

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

 

比如,如果你想在一个日期/时间值中增加一小时,可以使用hh缩写。在某些情况下,日期/时间部分支持两个缩写,如周可以用wk或ww支持。

 

<number>占位符指的是所增加的数值(一个整数)。比如,如果在日期中增加10天,就是10。但是,注意,如果是减去时间间隔,它必须是一个负整数。比如,从天数中减去10,就必须是-10。

 

<date>占位符指的是增加或减少的指定间隔的日期/时间值。它可能是一个日期/时间格式的字符串值,或者是方法返回的一个日期/时间值,又或者是常见的DATETIME或SMALLDATETIME字段。

 

让我们举例来说明它是如何工作的。在下面的SELECT语句中,我增加三个月到Sales.Orders表中OrderDate值:

 


SELECT OrderDate, DATEADD(mm, 3, OrderDate) AS NewDate 
FROM Sales.Orders 
WHERE OrderID = 1001

 

注意,SELECT列表使用了DATEADD方法。这个方法有三个参数:mm指月,3指月数,而OrderDate是一个DATETIME值。因此,当查询返回值时,每个OrderDate值都会增加三个月时间,如下的结果所示:

 

OrderDate

NewDate

2008-08-27 13:36:16.280

2008-11-27 13:36:16.280

 

如上所示,日期August 27已经被改为November 27。而且,这样的运算还不仅限于日期。下面我在OrderDate值中增加三个小时:

 


SELECT OrderDate, DATEADD(hh, 3, OrderDate) AS NewTime 
FROM Sales.Orders 
WHERE OrderID = 1001

 

DATEADD的第一个参数现在是hh,而不是mm,因此,只有小时被改变了,如下结果所示:

 

OrderDate

NewTime

2008-08-27 13:36:16.280

2008-08-27 16:36:16.280

 

日期/时间值也可以减去一定的日期或时间间隔。在下例中,我从OrderDate值中减去了三天:

 


SELECT OrderDate, DATEADD(dd, -3, OrderDate) AS PastDate 
FROM Sales.Orders 
WHERE OrderID = 1001

 

注意,DATEADD的第一个参数现在是dd。同时,注意,第二个参数是一个负数,这意味着将有三天被减去,如下所示:

 

OrderDate

PastDate

2008-08-27 13:36:16.280

2008-08-24 13:36:16.280

 

这样,新的日期是August 24而不是August 27。

 

这样,上面的例子演示如何在从数据库查询到日期/时间值后再对它进行修改。而DATEADD方法同样也可以用来插入日期/时间数据。因为DATEADD方法返回一个DATETIME值。(如果所提供的日期对应的方法是SMALLDATETIME,那么它将返回一个SMALLDATETIME值。)在下面的例子中,我添加了一行数据到Sales.Orders表中,然后使用SELECT语句来检索这个行:

 


INSERT INTO Sales.Orders 
VALUES(1002, GETDATE(), DATEADD(dd, 10, GETDATE())) 
GO 
SELECT * FROM Sales.Orders 
WHERE OrderID = 1002

 

注意,VALUES子句包含了表中每个字段的值。对于OrderDate值,我使用GETDATE()方法来获取当前的日期和时间。对于DelivDate字段,我使用DATEADD方法以及相应的三个参数。第一个参数dd表示将要添加到日期中的是天数。第二个参数10意味着将添加10天到日期中。最后,第三个参数是GETDATE方法。因此,10天将添加到目前的日期和时间中并插入到DelivDate字段。这就是SELECT语句生成的结果:

 

OrderID

OrderDate

DelivDate

1002

2008-08-27 13:40:22.357

2008-09-06 13:40:22.357

 

正如所期待的,DelivDate值比OrderDate晚10天。

 

现在让我们来检测一个使用了DATEADD方法的UPDATE语句。在下面的语句中,我从DelivDate值中减去了三天,然后显示了结果:

 


UPDATE Sales.Orders 
SET DelivDate = DATEADD(dd, -3, DelivDate) 
WHERE OrderID = 1002 
GO 
SELECT * FROM Sales.Orders 
WHERE OrderID = 1002

 

这次我在SET子句中使用了DATEADD——我将DelivDate值设为DATEADD方法返回的结果。这个方法指定天数(dd)为第一个参数,-3为第二个参数,而DelivDate字段为第三个参数。这就意味着该方法将返回一个比原始日期早三天的日期,并将DelivDate设置为新的日期,如下结果显示:

 

OrderID

OrderDate

DelivDate

1002

2008-08-27 13:40:22.357

2008-09-03 13:40:22.357

 

你应该记得,INSERT语句(在前一个例子)添加了一个DelivDate值为September 6的行。但是,这个值现在是September 3,比原来早了三天。

使用DATEDIFF方法

 

DATEDIFF方法可以计算两个日期之间的时间间隔,并返回一个代表间隔的整数。这个方法使用下面的语法:

 


DATEDIFF(<date/time_part>, <start_date>, <end_date>)

 

<date/time_part>占位符指的是两个日期中需要比较的部分。比如,你想确认开始日期和结束日期之间的小时数或天数。

 

除了工作日(dw, w)缩写之外,<date/time_part>占位符使用的缩写与DATEADD方法一样。DATEDIFF不支持工作日比较。

 

<start_date>占位符指的是比较的开始日期,而<end_date>占位符指的是结束日期。换言之,方法将返回开始日期和结束日期之间的具体时间或日期间隔。

 

让我们举例来说明它是如何工作的。下面的SELECT语句计算了Sales.Orders 表中OrderDate和DelivDate值之间的时间间隔:

 


SELECT OrderDate, DelivDate,  
DATEDIFF(dd, OrderDate, DelivDate) AS DaysDiff 
FROM Sales.Orders 
WHERE OrderID = 1002

 

在这个语句中,我在SELECT列表中使用了DATEDIFF。这个方法的第一参数指定间隔必须是天数(dd),而第二个参数指定OrderDate作为开始日期,然后第三个参数指定DelivDate作为结束日期。因此,DATEDIFF将计算OrderDate和DelivDate之间的天数,在此例子中,它是7天,如下结果所示:

 

OrderDate

DelivDate

DaysDiff

2008-08-27 13:40:22.357

2008-09-03 13:40:22.357

7

 

当然,它也可以用来计算各种时间间隔,如下例的语句所示:

 


SELECT OrderDate, DelivDate,  
DATEDIFF(hh, OrderDate, DelivDate) AS HoursDiff 
FROM Sales.Orders 
WHERE OrderID = 1002

 

在这种情况下,方法的第一个参数是小时(hh)而非天数。因此,方法将返回OrderDate 和DelivDate值之间相差的小时数,如下结果所示:

 

OrderDate

DelivDate

HoursDiff

2008-08-27 13:40:22.357

2008-09-03 13:40:22.357

168

 

两个值之间相差168个小时。

 

与DATEADD方法一样,DATEDIFF方法并不仅限于用在SELECT语句中。比如,DATEDIFF可以用在UPDATE语句的WHERE子句中,以确定哪一行需要更新。在下例中,我使用了DATEDIFF来指定这些在OrderDate和DelivDate值之间天数少于8的行。

 


UPDATE Sales.Orders 
SET DelivDate = DATEADD(dd, 3, DelivDate) 
WHERE DATEDIFF(dd, OrderDate, DelivDate) < 8 
GO 
SELECT OrderID, OrderDate, DelivDate,  
DATEDIFF(dd, OrderDate, DelivDate) AS DaysDiff 
FROM Sales.Orders

 

在前面的例子中,DATEDIFF方法返回了OrderDate和DelivDate值之间的天数。然后这个数目将与8作比较。如果天数少于8,那么这一行将被更新;否则,该行将不改变。对于这些需要更新的行,我使用DATEADD方法来增加三天到DelivDate值中。然后,我运行一个SELECT语句来返回Sales.Orders表的数据以及计算每个行中两个日期的不同,如下结果所示:

 

OrderID

OrderDate

DelivDate

DaysDiff

1001

2008-08-27 13:36:16.280

2008-09-08 18:27:10.750

12

1002

2008-08-27 13:40:22.357

2008-09-06 13:40:22.357

10

 

结果显示现在在两个日期(第二行中)之间是相差10天,而非原来的7天。

 

在表定义中使用DATEADD和DATEDIFF

 

DATEADD和DATEDIFF方法也可以用在表定义中。例如,字段定义的DEFAULT子句中可以用DATEADD方法或使用DATEDIFF方法来创建一个计算得来的字段。在下面的Transact-SQL代码中,我首先创建了使用DATEADD和DATEDIFF的表,然后添加一行数据到表中,最后检索表的数据:

 


USE AdventureWorks 
GO 
IF EXISTS (SELECT table_name  
FROM information_schema.tables  
WHERE table_schema = 'Sales' 
AND table_name = 'Orders') 
DROP TABLE Sales.Orders 
GO 
CREATE TABLE Sales.Orders 
( 
OrderID INT NOT NULL, 
OrderDate DATETIME NOT NULL DEFAULT GETDATE(), 
DelivDate DATETIME NOT NULL DEFAULT DATEADD(dd, 10, GETDATE()), 
DaysDiff AS DATEDIFF(dd, OrderDate, DelivDate) 
 
GO 
INSERT INTO Sales.Orders(OrderID) 
VALUES(1001) 
GO 
SELECT OrderID, OrderDate, DelivDate, DaysDiff 
FROM Sales.Orders

 

在CREATE TABLE语句中,我创建了四个字段,其中三个存储日期/时间数据。OrderDate字段直接使用GETDATE来生成默认值。DelivDate字段也有一个默认值。然而,这个默认值是基于DATEADD返回的结果的,同时,在这种情况下,我使用方法增加10天到GETDATE返回的值存储到DelivDate字段中。最后,DaysDiff字段是一个计算得来的字段,它的值是使用DATADIFF来计算OrderDate和DelivDate值之间的天数差。

 

在表定义之后,我插入一数据行到表中。因为所有的日期/时间值都是自动生成的,因此我仅仅需要插入OrderID值,如下所示:

 

OrderID

OrderDate

DelivDate

DaysDiff

1001

2008-08-27 13:42:50.433

2008-09-06 13:42:50.433

10

 

DATEADD和DATEDIFF方法不仅仅在表定义中非常有用,同样也适用于查询和数据修改语句。通过DATEADD,我们可以将日期/时间值增加和减少一定值,而通过DATEDIFF,我们可以计算日期/时间值之间的时间间隔。更多详细的关于这些方法的信息,可以阅读Microsoft SQL Server Books Online。

相关文章推荐

使用DATEADD和DATEDIFF来计算SQL Server的DATETIME值

在SQL Server数据库中,DATETIME和SMALLDATETIME值是以整数存储的。然而,与整数不同的是,它们不能直接地进行数学运算。尽管如此,有时候还是需要在日期/时间值中添加或减去一个时...

SQL Server 日期的加减函数: DATEDIFF DATEADD

SQL Server 日期的加减函数: DATEDIFF    DATEADDDATEDIFF: 返回跨两个指定日期的日期边界数和时间边界数, 语法:DATEDIFF ( datepart , sta...
  • xyzqiang
  • xyzqiang
  • 2011年06月30日 17:50
  • 103163

SQL Server中 DateDiff计算时间差

 转自:http://blog.163.com/happy_2010_zyj/blog/static/1511487562010424113143364/ DATEDIFF 函数 [日期和...

SQL中DATEADD和DATEDIFF的使用方法

DATEADD函数计算一个日期通过给时间间隔加减来获得一个新的日期,DATEDIFF函数计算两个日期之间的小时、天、周、月、年等时间间隔总数。 SQL Server DATEADD() 函数 ...

SQL server 和C#中的DateTime使用小结

  • 2009年04月18日 11:00
  • 63KB
  • 下载

SQL中DATEADD和DATEDIFF的用法

  • 2010年05月31日 18:46
  • 37KB
  • 下载

SQL Server 常用日期函数DateDiff()、GetDate() 以及oracle 日期常用函數 (SYSDATE、日期格式)等详解

SQL Server 常用日期函数DateDiff()、GetDate() 以及oracle 日期常用函數 (SYSDATE、日期格式)等详解 一、GetDate() 函数 作...

SQL SERVER 获取DB中datetime类型值以String形式显示

SQL SERVER 数据库中经常遇到一个需求:获取DB中datetime类型

sql server中datetime默认值设置和日期函数

select CONVERT(varchar, getdate(), 120 )         ----------------->>SQL语句 2004-09-12 11:06:08    --...

SQL中DATEADD和DATEDIFF的用法

通常,你需要获得当前日期和计算一些其他的日期,例如,你的程序可能需要判断一个月的第一天或者最后一天。你们大部分人大概都知道怎样把日期进行分割(年、月、日等),然后仅仅用分割出来的年、月、日等放在几个函...
  • upi2u
  • upi2u
  • 2014年07月15日 22:05
  • 1219
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:使用DATEADD和DATEDIFF来计算SQL Server的DATETIME值
举报原因:
原因补充:

(最多只允许输入30个字)