mysql2000日期加减,SQL日期处理函数DATEADD和DATEDIFF详解

SQL Server数据库中，DATETIME和SMALLDATETIME的值是存储为整数的。虽然如此，SQL Server数据库并不因此支持对这两种类型的值做数学运算。

不过，我们一定会有给日期或时间值增加或减去一定时间差的需求。例如，你可能想给某个日期时间值增加几个月或几天，甚至加几个小时。你甚至可能想比较两个日期时间值来了解他们之间的时间有多长，例如判断相差几天或者几年。Transact-SQL(T-SQL)提供了两个重要的日期时间函数——DATEADD 和 DATEDIFF，这两个函数可以帮助你轻松执行这类型计算。

本文详细介绍如何使用这两个函数，并提供一些例子展示它们的用法。为了演示这些功能，我使用T-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或者SMALLDATETIME类型的值增加(或者减去)一定的时间值。例如，你可能想给指定日期加减一个月。你可以使用DATEADD函数执行这个计算。这个函数的使用语法如下：

DATEADD(, , )

例如，如果你想给某日期时间值增加一个小时，你应该使用“hh”。有些时候，日期时间支持两种格式缩写，例如“week”，可以用“wk”或者“ww”。

占位符的作用是给日期增加一定的天数。例如，如果你想给某个日期至增加10天，你可以设置参数为“10”。不过要注意的是，如果你想减去一个时间差，你应该使用负整数。例如，给日期值减去10天应该把参数设置为“-10”。

占位符代表了要给日期时间值增加的差值。可以是日期时间格式的字符串值，也可以是某个函数返回的日期时间值，更多的时候是DATETIME或者SMALLDATETIME字段类型值。

我们来看一个例子。在下面的SELECT语句中，我给Sales.Orders表的OrderDate字段值增加了3个月：

SELECT OrderDate, DATEADD(mm, 3, OrderDate) AS NewDate

FROM Sales.Orders

WHERE OrderID = 1001

要注意的是，SELECT内容中包含了DATEADD函数。该函数有三个参数：“mm”表示月，“3”表示增量值，“OrderDate”是要处理的日期时间值。执行查询之后，结果显示返回值在“OrderDate”原始值的基础上加了3个月。

你可以看到日期“8月27”修改成了“11月27”。当然你不只可以修改日期，还可以修改“OrderDate”的小时值：

SELECT OrderDate, DATEADD(hh, 3, OrderDate) AS NewTime

FROM Sales.Orders

WHERE OrderID = 1001

DATEADD函数的第一个参数设置为“hh”，不是“mm”了，所以只会修改小时值。请看下表：

你还可以减去一定的日期时间值。在下面的例子中，我把OrderDate值减去了3天：

SELECT OrderDate, DATEADD(dd, -3, OrderDate) AS PastDate

FROM Sales.Orders

WHERE OrderID = 1001

请注意DATEADD函数的第一个参数现在设置为“dd”了，而且第二个参数设置的是负值，也是就是减去3天。

我们可以看到，函数运算后原来的日期“8月27日”变成了“8月24日”。

到目前为止，上面的例子展示了从数据库中查询值并做修改日期时间值的运算。你还可以使用DATEADD函数插入日期时间数据，因为该函数返回的是DATETIME类型的值(如果提供给函数入参的是SMALLDATETIME类型值，返回结果也会是SMALLDATETIME)。下面的例子我们将给Sales.Orders表里增加一行数据(使用了DATEADD函数)，然后执行查询语句。

INSERT INTO Sales.Orders

VALUES(1002, GETDATE(), DATEADD(dd, 10, GETDATE()))

GO

SELECT * FROM Sales.Orders

WHERE OrderID = 1002

请看VALUES从句中提供了该表每个字段对应的值，对于OrderDate字段，我用“GETDATE()”函数获取当前日期时间进行填充。对于DelivDate字段，我使用DATEADD函数处理当前日期后赋值。函数中传入3个参数，第一个参数“dd”表示按天数增加；第二个参数“10”表示给当前日期增加几天；第三个参数用“GETDATE()”函数取了当前日期。结果，就是在当前日期时间基础上增加10天插入到DelivDate字段了。

接下来，我们在UPDATE语句中使用DATEADD函数。在下面的语句中，我在DelivDate值的基础上减去3天然后查询显示该条数据：

UPDATE Sales.Orders

SET DelivDate = DATEADD(dd, -3, DelivDate)

WHERE OrderID = 1002

GO

SELECT * FROM Sales.Orders

WHERE OrderID = 1002

上面的例子中我在UPDATE的SET语句中使用了DATEADD函数，设置DelivDate的值为DATEADD函数返回值。该函数指定参数为“dd”、“-3”、“DelivDate”，表示获取比“DelivDate”日期值早3天的日期。

我们可以回顾一下，前面的例子中我们用INSERT语句插入DelivDate值是9月6日，现在返回的是9月3日，日期函数处理后减去了3天。

使用DATEDIFF函数

DATEDIFF函数计算两个日期之间的时间间隔，用返回的整数表示。该函数语法如下：

DATEDIFF(, , )

占位符代表了希望度量两个日期之间差值的单位。例如，你可能想了解开始日期和结束日期的小时数和天数。

占位符使用的缩写与DATEADD函数相同，除了周weekday的缩写(dw，w)。DATEDIFF函数不支持weekday选项。

占位符表示日期跨度的起始日期，占位符代表结束日期。换句话说，函数会计算这两个参数之间的时间或日期差。

我们再看一个例子。下面的SELECT语句计算Sales.Orders 表中字段OrderDate和DelivDate之间的时间差。

SELECT OrderDate, DelivDate,

DATEDIFF(dd, OrderDate, DelivDate) AS DaysDiff

FROM Sales.Orders

WHERE OrderID = 1002

在这个语句中，我使用DATEDIFF作为SELECT语句查询内容之一。该函数第一个参数指定了获取的间隔单位是天(dd)；第二个参数是OrderDate表示起始日期；第三个参数是DelivDate表示结束日期。DATEDIFF函数计算后会返回OrderDate和DelivDate之间相差的天数，本例中是7天。

当然你还可以计算相差的小时数，请看下面的语句：

SELECT OrderDate, DelivDate,

DATEDIFF(hh, OrderDate, DelivDate) AS HoursDiff

FROM Sales.Orders

WHERE OrderID = 1002

在这个例子中，函数的第一个参数时小时(hh)，而不是天。执行之后，函数会返回OrderDate和DelivDate之间的小时数。

与DATEADD函数类似，DATEDIFF函数并不只是可以用于SELECT语句。例如：你可以在UPDATE的WHERE语句中使用DATEDIFF函数来判断更新哪些行。在下面的例子中，我使用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，就更新该行数据，否则就不更新。对于符合更新条件的行，我使用DATEADD函数给DelivDate值增加3天。然后再次运行查询语句查询该表数据，计算每行两个日期字段相差天数。

使用DATEADD和DATEDIFF定义表

DATEADD 和 DATEDIFF函数还可以用在表定义脚本中。例如，你可以使用DATEADD函数设置字段默认值，或者使用DATEDIFF创建一个计算列。在下面的T-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

在建表语句中，我创建了4个字段，其中3个是日期时间类型。OrderDate列使用GETDATE生成默认值。DelivDate列也生成默认值，不过使用了DATEADD函数，本例中我们给GETDATE返回的当前时间增加了10天存储到DelivDate字段中。最后我们用DATEDIFF计算前面两个日期字段的日期差提供给DaysDiff字段。

创建表之后，我给表里插入了一行数据，其中的日期时间字段会自动生成，我只需要插入OrderID就可以了。

DATEADD 和 DATEDIFF这两个函数不只可以用于定义表，还可以用于查询和数据更新语句。使用DATEADD，我们可以对指定日期时间值进行加减处理，用DATEDIFF可以计算两个日期时间之间的天数或时间差。还有访问微软开发者网站了解更多关于这两个函数在Azure SQL数据库或者Azure SQL数据仓库中的应用。