sql datediff_SQL日期格式概述； DateDiff SQL函数，DateAdd SQL函数等

sql datediff

SQL date format functions like the DateDiff SQL function and DateAdd SQL Function are oft used by DBAs but many of us never took the time to fully understand these extremely useful features. For professionals just getting started with SQL Server, these functions are some of the first to become familiar with. So hopefully this article will have a little something for everyone across the skill spectrum

DBA通常使用诸如DateDiff SQL函数和DateAdd SQL函数之类SQL日期格式函数，但是我们许多人从未花时间充分理解这些极其有用的功能。 对于刚开始使用SQL Server的专业人员，这些功能是第一个熟悉的功能。 因此，希望本文对所有技能领域的人有所帮助

One of the most interesting data types that are supported in the relational database world is DateTime. In this article, we’re going to take a look at working with date time data types in SQL Server. We’ll understand the basics of date-time data-type and also, we’ll see a various examples of how to query the date-time fields using built-in functions within SQL Server for manipulating the data, transforming date-time values and in few cases, perform arithmetic operations.

关系数据库世界中支持的最有趣的数据类型之一是DateTime。 在本文中，我们将研究在SQL Server中使用日期时间数据类型。 我们将了解日期时间数据类型的基础知识，还将看到有关如何使用SQL Server中的内置函数查询日期时间字段以处理数据，转换日期时间值的各种示例。并在少数情况下执行算术运算。

First, let’s go ahead take a look at some popular SQL date format and time functions.

首先，让我们继续看一些流行SQL日期格式和时间函数。

By default, SQL Server inherently supports the languages that are supported by the Windows Operating System. Depending on locale and collation settings when you do the installation of SQL Server, the SQL date format display may be different. The kind of the data you’re going to play is determined based on locale setting and SQL Server collation setting.

默认情况下，SQL Server固有地支持Windows操作系统支持的语言。 安装SQL Server时，取决于语言环境和排序规则设置，SQL日期格式显示可能会有所不同。 根据区域设置和SQL Server排序规则设置确定要播放的数据类型。

In SQL Server, the data type DATE has two default Neutral Language Formats

在SQL Server中，数据类型DATE具有两种默认的中性语言格式

1. ‘YYYYMMDD’

   “ YYYYMMDD”

   The following example, the HumanResource.Employee table is queried to see the SQL date format of the HireDate values

   下面的示例查询HumanResource.Employee表以查看HireDate值SQL日期格式

   USE AdventureWorks2014;
   GO
    
   SELECT HireDate,BusinessEntityID,LoginID, JobTitle
   FROM HumanResources.Employee;
   GO
   

   Now, query the HireDate column using neutral language format. In the query, we’re passing integer value that consists of year, 2008; month, 12 and the 7^th day.

   现在，使用中性语言格式查询HireDate列。 在查询中，我们传递的是由year，2008组成的整数值； 月，第12和^第 7天。

   SELECT HireDate,BusinessEntityID,LoginID, JobTitle
   FROM HumanResources.Employee
   WHERE HireDate = '20081207';
   

   
   
   
   

   Note: It basically means that the input value is numeric; the SQL engine internally converts it and actually ends up with a character value. So, it is surrounded by the single quotes

   注意：基本上意味着输入值是数字； SQL引擎在内部对其进行转换，并最终以字符值结尾。 因此，它被单引号包围

2. ‘MM-DD-YYYY’ ( US-Based Format)

   'MM-DD-YYYY'（基于美国的格式）

   In this SQL date format, the HireDate column is fed with the values ‘MM-DD-YYYY’. This is not an integer format. Let us run the same query with the new SQL date format of input value ‘12-07-2008’ to the HireDate column. So let’s run the following query and verify the output. The output is the same set of five records.

   在此SQL日期格式中，为HireDate列提供了值'MM-DD-YYYY'。 这不是整数格式。 让我们使用向HireDate列输入值'12 -07-2008'的新SQL日期格式运行相同的查询。 因此，让我们运行以下查询并验证输出。 输出是五个记录的相同集合。

   SELECT HireDate,BusinessEntityID,LoginID, JobTitle
   FROM HumanResources.Employee
   WHERE HireDate = '12-07-2008';
   GO
   

   
   
   
   

   Note: The neutral language format values are implicit and SQL Server will do the necessary conversion. Any other SQL date format, requires a proper conversion of operands or/and values.

   注意：中性语言格式的值是隐式的，SQL Server将进行必要的转换。 任何其他SQL日期格式都需要正确转换操作数或/和值。

The following examples display an error message due to the improper use of the SQL date format.

以下示例显示由于不正确使用SQL日期格式而导致的错误消息。

SELECT HireDate,BusinessEntityID,LoginID, JobTitle
FROM HumanResources.Employee
WHERE HireDate = '25-12-2008';

Now, the operand and its values are both converted to a standard format 103. You can refer here for more information about CAST and Convert functions. The following example returns all the rows of the employee where HireDate ’25-12-2008’

现在，操作数及其值都转换为标准格式103。有关CAST和Convert函数的更多信息，请参见此处 。 以下示例返回员工的所有行，其中HireDate '25 -12-2008'

SELECT HireDate, 
       BusinessEntityID, 
       LoginID, 
       JobTitle
FROM HumanResources.Employee
WHERE CONVERT(DATE, HireDate, 103) = CONVERT(DATE, '25-12-2008', 103);

Now let’s start taking a look at some of the date functions that SQL Server makes available for us and these date functions allow us to work with the different parts of the dates and even manipulate some of the functionality that we can work with in terms of dates.

现在，让我们开始看一下SQL Server提供给我们的一些日期函数，这些日期函数使我们能够处理日期的不同部分，甚至操纵一些可以使用日期的功能。

Let’s look at the DATEPART SQL function. This function returns an integer value from the specified date column

让我们看一下DATEPART SQL函数。 此函数从指定的日期列返回一个整数值

Query the HireDate column to return only the year portion of the date

查询HireDate列以仅返回日期的年份部分

The following example generates an integer output column named [Year Part], and this should return only the year values

下面的示例生成一个名为[Year Part]的整数输出列，该列应仅返回年份值

SELECT DATEPART(YEAR, HireDate) AS [Year Part]
FROM HumanResources.Employee;
GO

Query the HireDate column to return only the month portion of the date

查询HireDate列以仅返回日期的月份部分

The following example generates an integer output column named [Month Part], and this should return only the month values

下面的示例生成一个名为[Month Part]的整数输出列，该列应仅返回月份值

SELECT DATEPART(MONTH, HireDate) AS [Month Part]
FROM HumanResources.Employee;

Query the HireDate column to return only the Quarter portion of the date

查询HireDate列以仅返回日期的Quarter部分

The following example generates an integer output column named [Month Part], and this should return only the month values

下面的示例生成一个名为[Month Part]的整数输出列，该列应仅返回月份值

SELECT DATEPART(QUARTER, HireDate) AS [Quarter Part]
FROM HumanResources.Employee;
GO

The following example returns the sales details for the specific day of the orderDate field. The example also shows the usage of the DATEPART SQL function and its few associated arguments.

下面的示例返回orderDate字段在特定日期的销售详细信息。 该示例还显示了DATEPART SQL函数的用法及其少量关联参数。

SELECT DATEPART(DAY, SOH.OrderDate) [Day Part], 
       DATEPART(MONTH, SOH.OrderDate) [Month Part], 
       DATEPART(QUARTER, SOH.OrderDate) [Quarter Part], 
       DATEPART(YEAR, SOH.OrderDate) [Year Part], 
       SUM(SOH.SubTotal) TotalSales
FROM sales.SalesOrderHeader SOH
     JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
GROUP BY DATEPART(DAY, SOH.OrderDate),  
     DATEPART(QUARTER, SOH.OrderDate), 
         DATEPART(YEAR, SOH.OrderDate), 
         DATEPART(MONTH, SOH.OrderDate)
ORDER BY DATEPART(QUARTER, SOH.OrderDate), 
         DATEPART(YEAR, SOH.OrderDate), 
         DATEPART(MONTH, SOH.OrderDate);

The SELECT statement selects the DATEPART, which is the function name, and it takes two arguments inside the parentheses. First, the datepart argument and the second, the date expression.

SELECT语句选择函数名称DATEPART，并在括号内使用两个参数。 首先是datepart参数，其次是日期表达式。

Let’s take a look at using the DATENAME SQL function. This is similar to a DATEPART SQL function, but it returns a character string from the specified date field.

让我们看一下使用DATENAME SQL函数。 这类似于DATEPART SQL函数，但是它从指定的日期字段返回一个字符串。

SELECT DATENAME(WEEKDAY, HireDate) [Day], DATENAME(WEEK, HireDate) [Week], DATENAME(MONTH, HireDate) [Month], DATENAME(YEAR, HireDate) [YEAR]
FROM HumanResources.Employee;
GO

Next, let’s take a look at the deterministic functions that returns date and time parts as an integer value. The following example returns integer values from the OrderDate field for the corresponding YEAR and MONTH functions.

接下来，让我们看一下将日期和时间部分返回为整数值的确定性函数。 下面的示例从OrderDate字段返回对应的YEAR和MONTH函数的整数值。

SELECT YEAR(SOH.OrderDate) as SalesYear,
         MONTH(SOH.OrderDate) as SalesMonth,
         SUM(SOH.SubTotal) AS TotalSales
 FROM sales.SalesOrderHeader SOH
     JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
GROUP BY YEAR(SOH.OrderDate),MONTH(SOH.OrderDate)
ORDER BY YEAR(SOH.OrderDate),MONTH(SOH.OrderDate)

The output is an aggregated value of the TotalSales based on monthly sales.

输出是基于月销售额的TotalSales的汇总值。

Now let’s take a look at a couple of other different functions. And this one we’re going to look at is called DATEDIFF. The DATEDIFF SQL function returns a signed integer value that allows us to determine elapsed time between two dates.

现在，让我们看一下其他几个不同的功能。 我们要看的这个叫做DATEDIFF。 DATEDIFF SQL函数返回一个带符号的整数值，该值使我们能够确定两个日期之间的经过时间。

Let us play with the query to use different date-parts to see the results.

让我们处理查询以使用不同的日期部分来查看结果。

SELECT DATEDIFF(YEAR, HireDate, GETDATE()) No_Of_Years, 
       DATEDIFF(MONTH, HireDate, GETDATE()) No_Of_Months, 
       DATEDIFF(quarter, HireDate, GETDATE()) No_Of_Quarters, 
       DATEDIFF(WEEK, HireDate, GETDATE()) Week_Of_Year, 
       DATEDIFF(DAYOFYEAR, HireDate, GETDATE()) Day_Of_Year
FROM HumanResources.Employee;
GO

The following example returns the time parts between the two DateTime fields. In this example, hours, minutes, and seconds between the DateTime values are calculated using DATEDIFF SQL function

下面的示例返回两个DateTime字段之间的时间部分。 在此示例中，使用DATEDIFF SQL函数计算DateTime值之间的小时，分​​钟和秒

SELECT 
    DATEDIFF( hour, GETDATE(),GETDATE()+1 ) AS Hours,
    DATEDIFF( minute,  GETDATE(), GETDATE()+1 ) AS Minutes,
    DATEDIFF( second,GETDATE(),GETDATE()+1 ) AS Seconds;
GO

如何在where子句中使用DATEDIFF SQL函数 (How to use the DATEDIFF SQL function in the where clause)

The following example returns all the employees who are working with the organization for more than 10 years. The conditional logic on the HireDate column is compared with greater than 120 months is mentioned in the where clause.

以下示例返回在组织中工作了10年以上的所有员工。 在where子句中提到了HireDate列上的条件逻辑与大于120个月的比较。

SELECT *
FROM HumanResources.Employee
WHERE DATEDIFF(MONTH, HireDate, GETDATE()) > 120;
GO

如何将DateDiff SQL函数与Aggregate函数一起使用 (How to use the DateDiff SQL function with an Aggregate function)

The following example returns the number of years between the first hire date and the last hire date. In this case, we’re looking for the minimum HireDate and the maximum HireDate aggregate function used as input parameters for DATEDIFF SQL function. With these values, one could easily find the number of years existed between the first time hire and the last time hire.

下面的示例返回第一个雇用日期和最后一个雇用日期之间的年数。 在这种情况下，我们正在寻找用作DATEDIFF SQL函数的输入参数的最小HireDate和最大HireDate聚合函数。 有了这些值，就可以轻松地找到第一次租用和最后一次租用之间存在的年数。

SELECT DATEDIFF(YEAR, MIN(HireDate), MAX(HireDate)) No_Of_Years
FROM HumanResources.Employee;

Let’s take a look at the DATEADD SQL function. The functions add or subtract the value to the specified datepart and return the modified value of the datepart.

让我们看一下DATEADD SQL函数。 函数将值加或减到指定的日期部分，并返回该日期部分的修改后的值。

Let’s take a look at the following example. The value 1 is added to the various datepart and the value -1 is added to the hour datepart and -30 are added to minute datepart.

让我们看下面的例子。 将值1添加到各个日期部分，将值-1添加到小时日期部分，将值-30添加到分钟日期部分。

SELECT 'TodayDate', 
       GETDATE()
UNION ALL
SELECT 'year', 
       DATEADD(year, 1, GETDATE())
UNION ALL
SELECT 'quarter', 
       DATEADD(quarter, 1, GETDATE())
UNION ALL
SELECT 'month', 
       DATEADD(month, 1, GETDATE())
UNION ALL
SELECT 'dayofyear', 
       DATEADD(dayofyear, 1, GETDATE())
UNION ALL
SELECT 'day', 
       DATEADD(day, 1, GETDATE())
UNION ALL
SELECT 'week', 
       DATEADD(week, 1, GETDATE())
UNION ALL
SELECT 'weekday', 
       DATEADD(weekday, 1, GETDATE())
UNION ALL
SELECT 'hour', 
       DATEADD(hour, -1, GETDATE())
UNION ALL
SELECT 'minute', 
       DATEADD(minute, -30, GETDATE())
UNION ALL
SELECT 'second', 
       DATEADD(second, 1, GETDATE())
UNION ALL
SELECT 'millisecond', 
       DATEADD(millisecond, 1, GETDATE())

In the output, we can see that the value is operated on the GETDATE() function as per the defined datepart.

在输出中，我们可以看到该值已按照定义的日期部分在GETDATE（）函数上进行操作。

Note: DATEADD and DATEDIFF SQL function can be used in the SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses.

注意：DATEADD和DATEDIFF SQL函数可以在SELECT，WHERE，HAVING，GROUP BY和ORDER BY子句中使用。

Datadiff与Datediff_BIG (Datadiff vs Datediff_BIG)

DATEDIFF	DATEDIFF_BIG
This is a very old function and it is available from the initial release SQL Server.	With the birth of SQL Server 2016, Microsoft has introduced a new DATEDIFF SQL function, an enhanced version of DATEDIFF known as DATEDIFF_BIG.
Syntax DATEDIFF(datepart, start_date, end_date)	Syntax DATEDIFF_BIG(datepart,start_date,end_date)
The return type of the DATEDIFF SQL function is INTEGER(4 bytes)	The return type of the DATEDIFF_BIG SQL function is BIGINT(8 bytes)
This function returns an integer value represents an elapsed time or day based on the defined DATEPART between the specified start DateTime and end DateTime	This function returns a BIG integer value represents an elapsed time or day based on the defined DATEPART between the specified start DateTime and end DateTime
The integer value ranges between -2,147,483,648 to +2,147,483,647	The BIGINT datatype can store a value between the range -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
Example: This example demonstrates the behavior of the DATEDIFF SQL function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value. SELECT DATEDIFF(MILLISECOND, '20180101', '20181231') AS 'DIFF DATA MILLISECOND';	Example: This example demonstrates the behavior of the DATEDIFF_BIG SQL function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

DATEDIFF	DATEDIFF_BIG
这是一个非常老的功能，可以从初始版本SQL Server中获得。	随着SQL Server 2016的诞生，Microsoft引入了新的DATEDIFF SQL函数，这是DATEDIFF的增强版本，称为DATEDIFF_BIG。
语法 DATEDIFF（datepart，start_date，end_date）	语法 DATEDIFF_BIG（datepart，start_date，end_date）
DATEDIFF SQL函数的返回类型为INTEGER （4个字节）	DATEDIFF_BIG SQL函数的返回类型为BIGINT（ 8个字节）
此函数返回一个整数值，该整数表示基于指定的开始日期时间和结束日期时间之间的已定义DATEPART的经过时间或日期	此函数返回一个BIG整数值，该整数表示基于指定的开始日期时间和结束日期时间之间定义的DATEPART的经过时间或日期
整数值在-2,147,483,648到+2,147,483,647之间	BIGINT数据类型可以存储-2 ^ 63（-9,223,372,036,854,775,808）到2 ^ 63-1（9,223,372,036,854,775,807）之间的值
示例：此示例演示了两个日期之间的毫秒差大于INT max（即2,147,483,647）值时DATEDIFF SQL函数的行为。	示例：此示例演示当两个日期之间的毫秒差大于INT max（即2,147,483,647）值时，DATEDIFF_BIG SQL函数的行为。 SELECT DATEDIFF_BIG(MILLISECOND, '20180101', '20181231') AS 'DIFF DATA MILLISECOND';
DECLARE @StartDate datetime2 = '20180101 00:00:00.0000000'; DECLARE @EndDate datetime2 = DATEADD(year, 100, @StartDate);   SELECT   'DATEDIFF',     DATEDIFF( year,@StartDate , @EndDate ) AS Years,   DATEDIFF( quarter, @StartDate, @EndDate ) AS Quarters,     DATEDIFF( month, @StartDate, @EndDate ) AS Months,     DATEDIFF( week, @StartDate, @EndDate ) AS Weeks,     DATEDIFF( dayofyear, @StartDate, @EndDate ) AS DayOfYear,     DATEDIFF( day, @StartDate, @EndDate ) AS Days,    DATEDIFF(MILLISECOND, @StartDate, @EndDate ) AS Millsecond,    DATEDIFF(MICROSECOND, @StartDate, @EndDate ) AS Micro,    DATEDIFF(NANOSECOND, @StartDate, @EndDate ) AS Nano	DECLARE @StartDate datetime2 = '20180101 00:00:00.0000000'; DECLARE @EndDate datetime2 = DATEADD(year, 100, @StartDate);   SELECT   'DATEDIFF_BIG',     DATEDIFF_BIG( year,@StartDate , @EndDate ) AS Years,   DATEDIFF_BIG( quarter, @StartDate, @EndDate ) AS Quarters,     DATEDIFF_BIG( month, @StartDate, @EndDate ) AS Months,     DATEDIFF_BIG( week, @StartDate, @EndDate ) AS Weeks,     DATEDIFF_BIG( dayofyear, @StartDate, @EndDate ) AS DayOfYear,     DATEDIFF_BIG( day, @StartDate, @EndDate ) AS Days,    DATEDIFF_BIG(MILLISECOND, @StartDate, @EndDate ) AS Millsecond,    DATEDIFF_BIG(MICROSECOND, @StartDate, @EndDate ) AS Micro,    DATEDIFF_BIG(NANOSECOND, @StartDate, @EndDate ) AS Nano

That’s all for now on this SQL date format article …

现在就此SQL日期格式文章就全部了……

结语 (Wrap Up)

Thus far, we have seen very important SQL date format functions such as DATEPART, DATENAME, YEAR, MONTH, and DAY, with particular emphasis on the DATEADD SQL function and the DATEDIFF SQL function. SQL date formatfunctions in SQL server are really powerful and really helps to deliver impactful data analytics and reports. In some cases, it’s a matter of adjusting collation settings, location settings, simply readjusting the input stream of text value, or correction made the data source would suffice the date time problem.

到目前为止，我们已经看到了非常重要SQL日期格式函数，例如DATEPART，DATENAME，YEAR，MONTH和DAY，尤其着重于DATEADD SQL函数和DATEDIFF SQL函数。 SQL Server中SQL日期格式功能非常强大，并且确实有助于提供有效的数据分析和报告。 在某些情况下，只需调整排序规则设置，位置设置，简单地重新调整文本值的输入流或进行校正即可使数据源满足日期时间问题。

It is recommended to use the neutral language format because it works better in most cases. At last, we see the difference between DATEDIFF SQL function and DATEDIFF_BIG SQL function. I hope you like this article. If you have any questions, feel free comment below

建议使用中性语言格式，因为它在大多数情况下效果更好。 最后，我们看到了DATEDIFF SQL函数和DATEDIFF_BIG SQL函数之间的区别。 希望您喜欢这篇文章。 如有任何疑问，请在下面随意评论

翻译自: https://www.sqlshack.com/sql-date-format-overview-datediff-sql-function-dateadd-sql-function-and-more/

sql datediff