Rajendra Gupta
examples of the DATEADD SQL function

DATEADD SQL function introduction and overview

May 6, 2019 by

This article explores the DATEADD SQL function and its usage scenarios with various examples.

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.

We can use the SQL SERVER DATEADD function to add or subtract specific period from a gives a date.

Syntax

DATEADD (datepart, number, date)

  • 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
  • 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

Suppose we want to add ten days to the current date. In this example, the variables would be

  • Datepart value: Day
  • Number: 5
  • Date: Getdate()Current date

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

QQ

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 OutputMsg 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.
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type int.

Data types in the SQL SERVER DATEADD function

We can use the following data type in date expression.

  • date
  • DateTime
  • datetimeoffset
  • 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.

examples of the DATEADD SQL function

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) .

We get the following output.

Examples

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.

Execute the following query to get the difference between starttime and endtime. We use the DATEADD SQL function along with the DATEDIFF SQL function.

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 Examples

We can use SQL Convert date format to represent this in HH:MM:SS format.

It gives the time difference in HH:MM:SS format as shown in the following image.

Examples

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.

DATEADD SQL Function Examples

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.

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.

Scalar sub queries and scalar functions with DateAdd SQL function

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.

Numeric expression

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 function

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
2,670 Views