sql dateadd函数
This article explores the DATEADD SQL function and its usage scenarios with various examples.
本文通过各种示例探索DATEADD SQL函数及其使用方案。
Usually, we work with date data type in SQL Server. We need to do manipulations with dates as well. In my previous article DATEPART SQL FUNCTION, we explored to retrieve specific part from the date such as day, year, month, quarter, day of the year.
通常，我们在SQL Server中使用日期数据类型。 我们还需要对日期进行操作。 在上一篇文章DATEPART SQL FUNCTION中 ，我们探索了从日期中检索特定的部分，例如日，年，月，季度，年中的某日。
We can use the SQL SERVER DATEADD function to add or subtract specific period from a gives a date.
我们可以使用SQL SERVER DATEADD函数从给定日期中添加或减去特定时间段。
句法 (Syntax )
DATEADD (datepart, number, date)
DATEADD（日期部分，数字，日期）
- Datepart: It specifies the part of the date in which we want to add or subtract specific time interval. It can have values such as year, month, day, and week. We will explore more in this in the example section Datepart ：它指定要在其中添加或减去特定时间间隔的日期部分。 它可以具有诸如年，月，日和周的值。 我们将在示例部分中对此进行更多探索
- Number: It is the number by which we want to increase or decrease date. It should be an integer 数字：这是我们要增加或减少日期的数字。 应该是整数
- Date: We can specify the date here. In this date, we want to add a specified number on datepart 日期：我们可以在此处指定日期。 在此日期，我们要在datepart上添加一个指定的数字
Suppose we want to add ten days to the current date. In this example, the variables would be
假设我们要在当前日期前增加十天。 在此示例中，变量为
- Day 天
- 5 5
- Date: Getdate() – Current date
- 日期： Getdate（） – 当前日期
通过示例探索SQL SERVER DATEADD函数 (Explore the SQL SERVER DATEADD function with examples)
Example | Datepart | Query with output |
Add 10 days to specified date | dd d | Output – 2019-05-09 00:00:00.000 |
Subtract one day from a specified date | dd D | Output -2019-04-28 00:00:00.000 |
Add two years in a specified date | YYYY YY | Output – 2021-04-29 00:00:00.000 |
Subtract three years from a specified date | YYYY YY | Output – 2016-04-29 00:00:00.000 |
Add 4 Months in specified date | MM | Output – 2019-09-29 00:00:00.000 |
Add 2 hours to a date | HH | Output – 2019-04-29 02:00:00.000 In this example, we did not specify any time with the input date. By default, SQL Server considers time 00:00.000 |
Subtract 120 minutes from date | MM | Output – 2019-04-28 22:00:00.000 |
Add 1 quarter in specified date | Output – 2019-07-29 00:00:00.000 | |
Add 100 days of the year in a specified date | DY | Output – 2019-08-07 00:00:00.000 |
Invalid value – Add 12578995 years in specified date | Year | We cannot specify invalid values or out of range values in the SQL SERVER DATEADD function. We get following error message Output – Msg 517, Level 16, State 1, Line 1: Adding a value to a ‘datetime’ column caused an overflow. |
Out of range number value – In the DATEADD number parameter, we can specify only integer values. | Integer value range: -231 (-2,147,483,648) to 231-1 (2,147,483,647) | In this example, we specified value 2147483648 in number parameter. This value does not fall inside an integer range. Once we execute this query, we get the following error message. |
例 | 日期部分 | 查询输出 |
在指定日期增加10天 | dd d | 产出– 2019-05-09 00：00：00.000 |
从指定日期减去一天 | dd d | 输出 -2019-04-28 00：00：00.000 |
在指定的日期增加两年 | YYYY YY | 输出– 2021-04-29 00：00：00.000 |
从指定日期减去三年 | YYYY YY | 产出– 2016-04-29 00：00：00.000 |
在指定日期增加4个月 | MM | 产出– 2019-09-29 00：00：00.000 |
将日期增加2个小时 | H | 产出– 2019-04-29 02：00：00.000 在此示例中，我们没有使用输入日期指定任何时间。 默认情况下，SQL Server考虑时间00：00.000 |
从日期减去120分钟 | MM | 产出– 2019-04-28 22：00：00.000 |
在指定日期增加1个季度 | 产出– 2019-07-29 00：00：00.000 | |
在指定日期添加一年中的100天 | DY | 产出 – 2019-08-07 00：00：00.000 |
值无效-在指定日期增加了12578995年 | 年 | 我们不能在SQL SERVER DATEADD函数中指定无效值或超出范围的值。 我们收到以下错误消息输出 – 消息517，级别16，状态1，第1行：向“ datetime”列添加一个值导致溢出。 |
超出范围的数字值–在DATEADD数字参数中，我们只能指定整数值。 | 整数值范围 ：-231（-2,147,483,648）到231-1（2,147,483,647） | 在此示例中，我们在number参数中指定了值2147483648。 该值不在整数范围内。 一旦执行此查询，我们将收到以下错误消息。 |
SQL SERVER DATEADD函数中的数据类型 (Data types in the SQL SERVER DATEADD function)
We can use the following data type in date expression.
我们可以在日期表达式中使用以下数据类型。
- date 日期
- DateTime 约会时间
- datetimeoffset 日期时间偏移
- datetime2 datetime2
- smalldatetime 小日期时间
- time 时间
In the following query, we declared a variable @date having a datetime2 datatype. We can use the DATEADD SQL function as per the following query.
在以下查询中，我们声明了一个具有datetime2数据类型的变量@date。 我们可以按照以下查询使用DATEADD SQL函数。
DECLARE @Date datetime2;
SET @Date = '2019-04-29 01:01:01.1111111';
Select DATEADD(quarter,1,@Date),
DATEADD(month,11,@Date)
,DATEADD(dayofyear,265,@Date)
, DATEADD(day,265,@Date)
, DATEADD(week,4,@Date)
, DATEADD(weekday,29,@Date)
, DATEADD(hour,11,@Date)
, DATEADD(minute,59,@Date)
, DATEADD(second,01,@Date)
, DATEADD(millisecond,1,@Date);
使用SQL SERVER DATEADD函数从指定日期范围内的表中获取记录 (Using the SQL SERVER DATEADD function to get records from a table in specified date range)
We can use the DATEADD SQL function to retrieve records from a table for a period. In the following query, we specified a date with parameter @Startdate. We want to retrieve records from Orders table. We need to get records between @StartDate and @Enddate ( add 1 hour in start date) .
我们可以使用DATEADD SQL函数从表中检索一段时间的记录。 在以下查询中，我们使用参数@Startdate指定了一个日期。 我们要从“订单”表中检索记录。 我们需要获取@StartDate和@Enddate之间的记录（在开始日期中添加1小时）。
DECLARE @StartDate DATETIME= '2013-05-01 11:00.000';
DECLARE @Hours INT= 1;
SELECT OrderID,
LastEditedWhen
FROM [WideWorldImporters].[Sales].[Orders]
WHERE LastEditedWhen BETWEEN @StartDate AND DATEADD(HOUR, @Hours, @StartDate);
We get the following output.
我们得到以下输出。
使用SQL SERVER DATEADD函数获取日期或时间差 (Using the SQL SERVER DATEADD function to get date or time difference)
We can use the DATEADD SQL function to get time difference between the start date and end date. Many times, we want to use the SQL SERVER DATEADD function to get date difference. For example, we want to know how long it took to complete an order or how long it took to reach from home to office.
我们可以使用DATEADD SQL函数获取开始日期和结束日期之间的时差。 很多时候，我们想使用SQL SERVER DATEADD函数来获取日期差。 例如，我们想知道完成订单需要多长时间，或者从家到办公室要花费多长时间。
Execute the following query to get the difference between starttime and endtime. We use the DATEADD SQL function along with the DATEDIFF SQL function.
执行以下查询以获取开始时间和结束时间之间的差异。 我们将DATEADD SQL函数与DATEDIFF SQL函数一起使用。
DECLARE @StartTime DATETIME= '2019-04-29 20:30:00', @EndTime DATETIME= '2019-04-30 01:00:00';
SELECT DATEADD(Minute,DATEDIFF(Minute, @StartTime, @EndTime),0) AS ElapsedTime;
It gives the elapsed time in minutes. We specified value 0 in the DateADD SQL function. It takes date value 1900-01-01 00:00:00.000
它以分钟为单位给出经过的时间。 我们在DateADD SQL函数中指定了值0 。 它需要日期值1900-01-01 00：00：00.000
We can use SQL Convert date format to represent this in HH:MM:SS format.
我们可以使用SQL Convert日期格式将其表示为HH：MM：SS格式。
DECLARE @StartTime DATETIME= '2019-04-29 20:30:00', @EndTime DATETIME= '2019-04-30 01:00:00';
SELECT CONVERT(VARCHAR(8), DATEADD(Minute, DATEDIFF(Minute, @StartTime, @EndTime), 0), 108) AS ElapsedTime;
It gives the time difference in HH:MM:SS format as shown in the following image.
如下图所示，它以HH：MM：SS格式给出了时差。
将SQL SERVER DATEADD函数结果指定为新列 (Specify the SQL SERVER DATEADD function result as a new column )
We can use the SQL SERVER DATEADD function to get a new column after adding the required date value. In the following query, we want to add two days in the start date and represent this as a new column.
添加所需的日期值后，我们可以使用SQL SERVER DATEADD函数获取新列。 在以下查询中，我们要在开始日期添加两天，并将其表示为新列。
DECLARE @StartDate DATETIME= '2013-05-01 11:00.000';
DECLARE @Day INT= 2;
SELECT OrderID,
LastEditedWhen,
DATEADD(day, @Day, LastEditedWhen) AS NewDateColumn
FROM [WideWorldImporters].[Sales].[Orders];
DATEADD SQL函数中的标量子查询和标量函数 (Scalar sub queries and scalar functions in the DATEADD SQL function)
We can use the SQL SERVER DATEADD function with scalar sub queries and scalar functions as well. In the following query, we want to add number of days in max date value of LastEditedWhen in the Sales.orders table.
我们可以将SQL SERVER DATEADD函数与标量子查询和标量函数一起使用。 在以下查询中，我们要在Sales.orders表的LastEditedWhen的最大日期值中添加天数。
The first subquery gives the number 16496 and second sub query provides a date. We used the SQL SERVER DATEADD function to add number of days, and we get the following output.
第一个子查询提供数字16496，第二个子查询提供日期。 我们使用SQL SERVER DATEADD函数添加天数，并获得以下输出。
SELECT DATEADD(dd,
(
SELECT TOP 1 BusinessEntityID
FROM Person.Person
),
(
SELECT MAX(LastEditedWhen)
FROM [WideWorldImporters].[Sales].[Orders]
));
SQL SERVER DATEADD函数中的数值表达式 (Numeric expressions in the SQL SERVER DATEADD function)
We can use numeric expressions as well in SQL SERVER DATEADD function. In the following query, we use a numeric expression to calculate the number and give us an output.
我们也可以在SQL SERVER DATEADD函数中使用数字表达式。 在以下查询中，我们使用数字表达式计算数字并提供输出。
SELECT GETDATE() AS CurrentDate,
DATEADD(month, -(12 / 6), GETDATE()) AS ModifiedDate;
将DATEADD与Rank函数一起使用 (Using DATEADD with the Rank function)
We can use the DATEADD SQL function in a rank function as well. We can use it for the number argument. In the following query, you can see Row_Number() function to use number parameter and the values as date parameter.
我们也可以在rank函数中使用DATEADD SQL函数。 我们可以将其用于number参数。 在以下查询中，您可以看到Row_Number（）函数将数字参数和值用作日期参数。
SELECT OrderID,
CustomerID,
DATEADD(day, ROW_NUMBER() OVER(
ORDER BY CustomerPurchaseOrderNumber), SYSDATETIME()) AS 'Row Number'
FROM [WideWorldImporters].[Sales].[Orders];
结论 (Conclusion)
In this article, we explored various uses and examples of the DATEADD SQL function. It allows us to add or subtract datepart values from the specified date. I hope you like this article. Feel free to provide feedback in the comments below.
在本文中，我们探讨了DATEADD SQL函数的各种用法和示例。 它允许我们从指定日期添加或减去datepart值。 希望您喜欢这篇文章。 请随时在下面的评论中提供反馈。
翻译自: https://www.sqlshack.com/dateadd-sql-function-introduction-and-overview/
sql dateadd函数
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
