学习使用SQL Server日期函数

目录

SQL Server的日期函数简介

用于获取当前日期和时间的函数

GETDATE

获取日期部分的函数

DATENAME

DATEPART

修改和查找日期差异

DATEDIFF

DATEADD

---

SQL Server的日期函数为您提供了一组可用于操作日期的函数。该函数用于各种操作，例如向日期添加周数，计算两个日期之间的差异，或将日期分解为其基本部分。

本文的所有示例均基于Microsoft SQL Server Management Studio和AdventureWorks2012数据库。

SQL Server的日期函数简介

在SQL服务器中有超过二十五种不同的函数被归类为日期函数。

所有函数都列在日期函数（Transact-SQL）页面上。我建议访问该页面以了解每个函数。

我们将重点关注我在商业中常用的函数，而不是重复这些内容。

在下表中，我将函数和颜色编码分类。颜色代码对应于您在业务环境中使用该特定函数的可能性。绿色最有可能被使用，红色更少。

这不是一个严格的规模，并且所有函数在某些商业案例中都有用，但我想要一种方法来帮助您筛选出于那些最相关的领域。

这是我的尝试：

DateTime函数——最常见的是绿色

用于获取当前日期和时间的函数

在用于返回当前日期和时间的所有函数中，GETDATE函数是最常用的函数。

GETDATE

GETDATE函数用于返回服务器的当前数据和时间。当您需要将上次付款日期等数据与当前日期进行比较时，此函数会变得非常方便。

GETDATE函数的一般形式是

GETDATE()

这是一个使用GETDATE的例子

SELECT GETDATE()

返回2015-08-09 21：55：00.477

更有用的实例是使用GETDATE与DATEDIFF或DATEADD。查看DATEDIFF更全面的示例。

获取日期部分的函数

日期部分是指日期的各个组成部分，例如年，月或日。日期部分用于本节中的多个函数和其他函数。下面是一个日期部分表，可以在函数中使用，如DATENAME，DATEPART，DATEDIFF，和DATEADD。

日期部分值

这些名字非常明显。每个日期部分对应于众所周知的时期，例如月份或小时。

DATENAME

DATENAME是一个非常有用的函数，用于返回日期的各个部分，例如月份名称或对应于特定日期的星期几。

DATENAME的一般形式是

DATENAME(date part, value)

其中日期部分对应于预定义部分的列表，值是您使用的日期。

此函数返回一个字符值。

以下是一个显示各种日期部分的示例。

SELECT DATENAME(year, GETDATE()) as Year,
       DATENAME(week, GETDATE()) as Week,
       DATENAME(dayofyear, GETDATE()) as DayOfYear,
       DATENAME(month, GETDATE()) as Month,
       DATENAME(day, GETDATE()) as Day,
       DATENAME(weekday, GETDATE()) as WEEKDAY

假设GETDATE返回2015-08-10 12：56：25.313，示例查询结果为

DATENAME结果

注意：还有许多其他日期部分可以与DATENAME一起使用。您可以在MSDN站点上找到完整列表。

下面是一个示例，说明如何使用它DATANAME来执行订单的某些汇总级别分析。

假设销售经理想要每周销售的摘要。使用DATENAME函数我们可以提供该信息。

SELECT   DATENAME(year, OrderDate) as OrderYear,
         DATENAME(week, OrderDate) as OrderWeek,
         SUM(TotalDue) as WeeklySales
FROM     Sales.SalesOrderHeader
WHERE    DATENAME(year, OrderDate) = '2008'
GROUP BY DATENAME(year, OrderDate), DATENAME(week, OrderDate)
ORDER BY DATENAME(year, OrderDate), DATENAME(week, OrderDate)

结果如下

DATENAME——注意OrderWeek按字符值排序

我想指出，虽然我们按年和周排序，但周似乎不合时宜。这是因为OrderWeek返回的是一个字符值。为了按数字排序，我们需要将结果转换为数值。

我们可以轻松地在ORDER BY子句中转换值，但是一旦您了解DATEPART，就没有必要这样做了。

DATEPART

返回一个整数，表示日期的指定部分。它的工作方式非常相似DATENAME，因为您指定了日期部分和值，但是，它返回7而不是返回如“July”的文本值。

DATEPART的一般形式是

DATEPART(date part, value)

其中日期部分对应于预定义部分的列表，值是您使用的日期。

该函数返回一个整数。

以下是一个显示各种日期部分的示例。

SELECT DATEPART(YEAR, GETDATE()) as Year,
       DATEPART(WEEK, GETDATE()) as Week,
       DATEPART(DAYOFYEAR, GETDATE()) as DayOfYear,
       DATEPART(MONTH, GETDATE()) as Month,
       DATEPART(DAY, GETDATE()) as Day,
       DATEPART(WEEKDAY, GETDATE()) as WEEKDAY

假设GETDATE返回2015-08-10 12：56：25.313，示例查询结果为

DATEPART结果

注意：还有许多其他日期部分可以与DATEPART一起使用。您可以在MSDN站点上找到完整列表。

我们也可以像使用DATEPART获得销售总额的摘要，就像对DATENAME所做的那样。这是查询

SELECT   DATEPART(year, OrderDate) as OrderYear,
         DATEPART(week, OrderDate) as OrderWeek,
         SUM(TotalDue) as WeeklySales
FROM     Sales.SalesOrderHeader
WHERE    DATEPART(year, OrderDate) = 2008
GROUP BY DATEPART(year, OrderDate), DATEPART(week, OrderDate)
ORDER BY DATEPART(year, OrderDate), DATEPART(week, OrderDate)

您注意到这些结果根据周正确排序：

DATEPART结果按数字顺序排序

DAY, MONTH, YEAR

DAY，MONTH和YEAR函数根据提供的日期以整数形式返回日、月或年。

所有三个函数的一般形式相同。为简洁起见，我们只是展示DAY，其他两个函数以类似的方式工作。

这是它的一般形式：

DAY(value)

其中值是你希望的日期。

以下是一个显示这些函数的快速示例

SELECT HireDate,
       YEAR(HireDate) as HireYear,
       MONTH(HireDate) HireMonth,
       DAY(HireDate) as HireDay
FROM   HumanResources.Employee

结果如下：

DAY，MONTH和YEAR的结果

你可能已经注意到YEAR(value)是DATEPART(year, value)简写。两者都返回相同的结果，以下是一个你可以尝试的SQL语句：

SELECT HireDate,
       YEAR(HireDate) as HireYear,
       DATEPART(year, HireDate) as HireYearFromDatePart
FROM   HumanResources.Employee

你会发现MONTH和DAY也是类似的。

修改和查找日期差异

DATEDIFF

DATEDIFF函数返回两个日期之间的年数，月数，周数或天数。

DATEDIFF的一般形式是

DATEDIFF(date part, start value, end value)

其中日期部分对应于预定义日期部分的列表，例如年，月，周和日。

差值在起始值和结束值之间计算。

这个函数可能看起来有点复杂，但它确实值得理解，因为它在SQL中有许多实际用途。

您可以使用它来计算帐单过期的天数，或两个日期之间的天数或周数。

考虑这个例子：

人力资源副总裁希望授予所有员工多年的服务奖励。她希望您提供十年以上服务的所有员工的报告。

为此，我们将使用DATEDIFF报告显示服务年数，并过滤掉服务年限少于十年的报告。

这是SQL语句

SELECT   NationalIDNumber,
         HireDate,
         DATEDIFF(year, HireDate, GETDATE()) YearsOfService
FROM     HumanResources.Employee
WHERE    DATEDIFF(year, HireDate, GETDATE()) >= 10
ORDER BY YearsOfService DESC

结果如何

DATEDIFF结果

DATEADD

DATEADD函数用于在日期之间添加年，月，周或天。

DATEADD的一般形式是

DATEADD(date part,number,value)

其中日期部分对应于预定义日期部分的列表，例如年，月，周和日，而数字指定要添加到值中的日期部分的数量。

如果number为正数，则将该数量的日期部分添加到该值。

如果number为负数，则从值中减去日期部分的有效值。

这个函数可能看起来有点复杂，但它确实值得理解，因为它在SQL中有许多实际用途。

您可以使用它来计算账单到期之前的天数，或者计算未来或过去的天数或周数。

考虑这个例子：

销售经理一直在与生产经理谈论在网上销售商品。他们想要订购自行车。如果今天订购了自行车，何时可以将它们运送给客户？

如果您查看Product表，您会看到有一个DaysToManufacture字段。结合使用DATEADD和GETDATE()我们可以计算出未来的天数。

SELECT NAME,
       DaysToManufacture,
       GETDATE() as Today,
       DATEADD(day, DaysToManufacture, GETDATE()) EarliestDate
FROM   Production.Product
WHERE  DaysToManufacture > 1

结果是这样的

使用DATEADD将DAYS添加到DATE