DATEADD SQL Function to Add and Subtract Dates and Times

Problem

Storing date-time values is very crucial for various business logic and being able to manipulate dates can be very helpful. In this tutorial, we look at how to use the Micrsoft SQL Server DATEADD function to add and subtract from date and time data types.

Solution

Microsoft SQL Server offers multiple database objects to capture date-time data. Today we will talk about the Transact-SQL DATEADD SQL Server function in this tutorial. I will explore an overview and use cases to help you understand the use of this function in a SQL database.

The DATEADD function is used to manipulate SQL date and time values based on some specified parameters. We can add or subtract a numeric value to a specified date-time to get future or past timelines.

The syntax of this function is:

DATEADD (datepart, number, date)

datepart is a portion of the date to which a specified numeric value will be added
number is an integer value that will be added to the specified datepart
date is the value of a specified date to which specified numeric value will be added on specified datepart

The datepart is a part of the date such as the year, month, date, day, hour, minutes, second, etc.

Below is the list of datepart which can be used with the DATEADD function. I have also given its abbreviations which we can use in SQL Server like the abbreviation of Year is yy or yyyy so you can use any of all 3 options Year, yy, or yyyy to manipulate the year datepart. The output returned by all 3 options will be the same. Here is the list.

Datepart	Abbreviations
Year	yy, yyyy
Quarter	qq, q
Month	mm, m
Week	wk,ww
Weekday	w, dw
Day	dd,d
DayOfYear	dy, y
Hour	hh
Minute	mi, n
Second	ss, s
Millisecond	ms
Microsecond	mcs
Nanosecond	ns

Basic use case of DATEADD Function

I have shown all datepart entities which can be used with the SQL Server DATEADD function. Let’s understand it more using the below example where I have chosen datepart as Year, number as 2, and specified the date as today’s date. I have also given another example where current datetime is calculated using another date-time function GETDATE() as the specified date on which this function will do the calculation to return the result.

The below query will return the date after 2 years from the specified date. It means this function will add 2 years to the year portion of the specified date which is 2021 so the output should return 2023.

As I said above, I have used two approaches to define the current date, one is using the function GETDATE() whereas the second is using a specific date.

SELECT DATEADD(YEAR, 2, GETDATE())  AS [Result using getdate],
       DATEADD(YEAR, 2, '20211005') AS [Result using specified date]

The below output has returned 2 years after 5^th Oct 2021 which is 5^th Oct 2023. Both statements returned the same output if we do not consider the time part of the date-time.

sql server dateadd function example and results

As I stated above, we can also use datepart abbreviations in place of datepart names to fulfill our requirements. I have shown this in the below example where I have just changed the abbreviation of Year and used YY and YYYY to return the result.

SELECT DATEADD(YY,   2, GETDATE())  AS [Result using getdate],
       DATEADD(YYYY, 2, '20211005') AS [Result using specified date]

We can see the result is the same as the above output where we have used datepart name as Year.

Add Date using DATEADD SQL function

This section will explore how to add a numeric value to a datepart of the specified date. I have added a numeric value to multiple dateparts to get future date-time values.

The below query will display the future dates based on specified duration which is the number that we will add to a specified date. I have used MONTH, QUARTER, WEEK, and DAY as datepart, the number 2 as numeric figure or duration which we will add to the respective datepart to get 2nd day, week, quarter or month after the current date which can be returned by either GETDATE() or a specific date.

SELECT DATEADD(MONTH,   2, GETDATE())  AS [DateAfter2Month],
       DATEADD(QUARTER, 2, GETDATE())  AS [2ndQuarterAfterToday],
       DATEADD(WEEK,    2, '20211005') AS [2ndWeekAfterSpecifiedDate],
       DATEADD(DAY,     2, '20211005') AS [2ndDayAfterSoecifiedDate]

Below shows the portion of the specified date change which are defined in the DATEADD function. The first column is displaying date after two months since current date, the second column is displaying the second quarter since the current quarter, the third column is displaying the second week after the current week and the fourth column is showing the second day after the current date i.e., 5^th Oct 2021.

The above use case has demonstrated the day, months, and week part, lets do it for the time part of specified date like hours, minutes, and seconds.

I have also used the GETDATE() function to return the current date-time so that you can compare the results of the DATEADD function used in the below query.

The below query will return

current date-time
date-time after 2 hours since the current time
date-time after 2 minutes since the current time
date-time after 2 seconds since the current time

SELECT GETDATE()                     AS [CurrentDateTime],
       DATEADD(HOUR,   2, GETDATE()) AS [2hoursAfterSpecifiedTime], 
       DATEADD(MINUTE, 2, GETDATE()) AS [2MinutesAfterSpecifiedTime],
       DATEADD(SECOND, 2, GETDATE()) AS [2SecondsAfterSpecifiedTime]

Let’s analyze the output.

current date-time returned as 2021-10-05 10:28:01.310
date-time before 2 hours since current time has returned as 2021-10-05 12:28:01.310
date-time before 20 minutes since current time has returned as 2021-10-05 10:30:01.310
date-time before 20 seconds since current time has returned as 2021-10-05 10:28:03.310

You can get similar results for milliseconds, microseconds, and nanoseconds as well by specifying their datepart in the SQL Server DATEADD function.

Subtract Date using DATEADD SQL function

The above section has explained how to add to a date using the DATEADD function whereas this section will show the use cases of subtracting from a date.

We can also get the date-time values from the past by subtracting the numbers to their respective datepart of specified date-time. We need to use a minus (-) with the specified numbers like if you want to return date-time of last year then you can pass -1 to datepart Year.

The below query will return the date-time of the second last month from the current date. The current date will be calculated using another SQL Server function GETDATE(). Similarly, we get the second last quarter, second last week, and second last date from the current date of from a specific specified date. I have used -2 as the number to get the second last datepart values.

SELECT DATEADD(MONTH,   -2, GETDATE())  AS [2MonthsbeforeCurrentDate],
       DATEADD(QUARTER, -2, GETDATE())  AS [SecondLastQuarter],
       DATEADD(WEEK,    -2, '20211005') AS [SecondLastWeek],
       DATEADD(DAY,     -2, '20211005') AS [SecondLastDate]

Have a look at the output below.

We can use the same logic to subtract the date to get the time part of the specified date-time.

The below query will return

current date-time
date-time before 2 hours since current time
date-time before 20 minutes since current time
date-time before 20 seconds since current time

SELECT GETDATE()                       AS [CurrentDateTime],
       DATEADD(HOUR,    -2, GETDATE()) AS [2hoursBeforeSpecifiedTime], 
       DATEADD(MINUTE, -20, GETDATE()) AS [20MinutesBeforeSpecifiedTime],
       DATEADD(SECOND, -20, GETDATE()) AS [20SecondsBeforeSpecifiedTime]

Let’s analyze the output.

current date-time returned as 2021-10-05 10:36:15.960
date-time before 2 hours since current time has returned as 2021-10-05 08:36:15.960
date-time before 20 minutes since current time has returned as 2021-10-05 10:16:15.960
date-time before 20 seconds since current time has returned as 2021-10-05 10:35:55.96960

We can see above results below.

Use DATEADD SQL function with columns

Here, I will show you how to use this function with column values from a table. We will use table OrderDetails and suppose you have a requirement to get the list of products and their sell date, additionally when the company has delivered the product, its return end date, and warranty expiration date.

If the company has a delivery policy for all products within 3 days, warranty expiration is 6 months after the date of purchase, and return end date is within 3 weeks from its purchase.

We can use the below query to get this information. I have used the DATEADD function with DAY, MONTH, and WEEK datepart and the date column of table OrderDetails which stores the product sell date.

SELECT TOP 10
   ProductName,
   InvoiceMonth,
   Price,
   Date as [Sell Date],
   City,
   DATEADD(DAY,   3, Date) AS [DeliveryDate],
   DATEADD(MONTH, 6, Date) AS [WarrantyEndDate],
   DATEADD(WEEK,  3, Date) AS [ReturnEndDate]
FROM OrderDetails

We can see the details about each product and its purchase date in column Date. You can also see the additional columns that we added DeliveryDate, WarrantyEndDate, , and ReturnEndDate. You can compare the dates of each column to validate the output.

Next Steps

I have explored the DATEADD SQL function in this article along with use cases. I have also explained how to use this function with the values specified in a column. You can go ahead and practice to return results for other dateparts.
Check out these related date function tutorials:

Resources

Community

Subscribe