How to add or subtract dates in SQL Server

This article explores SQL Server functions to add or subtract dates in SQL Server.

Introduction

Dealing with dates is always a fascinating affair for me. When you work with dates, it’s essential to understand the basics to ensure that SQL queries have the expected data output

I often notice how difficult it is for beginners to find date formatting in SQL Server. For example, suppose you want information like “Give me the tenure of employees in months” from the employee database. How do you calculate the tenure from an employee’s start and end date?

Understanding some of the most commonly used functions like DATEADD, DATEDIFF and DATEDIFF_BIG will allow you to get what you want quickly without having to go into lengthy detail!

Requirements

It would help if you had the following things to work with this article.

SQL Server instance: You can use any SQL Server version. If you are new to SQL Server, you can download SQL Server 2019 express or developer edition to start learning.
SQL Server Management Studio or Azure Data Studio:
- SSMS Download link
- Azure Data Studio download link
Understanding of SQL Server data types: You should understand this article’s date or time-related data types. You can refer to the article, An overview of SQL Server data types for understanding various data types in SQL Server and their usage.
You can also download a Microsoft sample database called AdventureWorks to work with sample data. Refer to the link AdventureWorks sample databases to download the AdventureWorks database as per your SQL version.

To add or subtract dates, let’s explore the DATEADD, DATEDIFF, and DATEDIFF_BIG functions in SQL Server.

DATEADD Function in SQL Server

The DateAdd() function adds or subtracts a specified period(a number or signed integer) from a given date value.

Syntax:

DATEADD (datepart, number, date)

Datepart: The date part to which DATEADD adds a specified number. For example, if you want a date after 10 months from today, we will use month or mm datepart. Similarly, we can have the following datepart values.

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

Number: It can be an integer value or an expression returning an int value to add to the datepart. It can be a positive or negative value.

Date: The date argument can have value from the following data types.

Date
Datetime
Datetime2
datetimeoffset
smalldatetime
time

The following query uses DateAdd() function to return the date after the 1 month from the specified input date.

SELECT DATEADD(month, 1, '20220726') as query1;

SELECT DATEADD(month, 1, '2022-07-26') as query2;

Similarly, the query to add 10 days to August 1, 2022 (input date), the DATEADD() function would be:

SELECT DATEADD(DD, 10, '2022-08-01') as query1;

Let’s look at a few more examples and their output using comments. We specified different datepart values and integers to get the required data.

DECLARE @datetime datetime;

SET @datetime = getdate()

SELECT DATEADD(hour,23,@datetime); --2022-07-27 02:25:20.960

SELECT DATEADD(minute,59,@datetime); --2022-07-26 04:24:20.960

SELECT DATEADD(second,59,@datetime); --2022-07-26 03:26:19.960

SELECT DATEADD(day,365,@datetime); --2023-07-26 03:25:20.960

SELECT DATEADD(month,13,@datetime); --2023-08-26 03:25:20.960

SELECT DATEADD(quarter,4,@datetime); --2023-07-26 03:25:20.960

SELECT DATEADD(week,5,@datetime); --2022-08-30 03:25:20.960

SELECT DATEADD(dayofyear,365,@datetime); --2023-07-26 03:25:20.960

SELECT DATEADD(weekday,31,@datetime); --2022-08-26 03:25:20.960

The DatePart() function can also be used within the T-SQL statement. For example, in the query below, we add 1 day in the orderdate to calculate the shipping date.

SELECT SalesOrderID

,OrderDate

,DATEADD(day,1,OrderDate) AS PromisedShipDate

FROM Sales.SalesOrderHeader;

We can also specify scalar subqueries and scalar functions in the number and date argument. Look at the following query, and it uses the following scalar subqueries.

(SELECT TOP 1 BusinessEntityID FROM Person.Person) is for number argument. The value returned by the subquery is supplied to the number argument.
(SELECT MAX(ModifiedDate) FROM Person.Person) calculates the third parameter, i.e., date value.

SELECT DATEADD(month,(SELECT TOP 1 BusinessEntityID FROM Person.Person),

(SELECT MAX(ModifiedDate) FROM Person.Person));

You can use numeric expressions, unary operators, arithmetic operators, and scalar system functions for the number and date argument in DATEADD() function.

SELECT DATEADD(dd,-(14/2), SYSDATETIME());

As specified earlier, we can use date, DateTime, and datetime2 datatypes as input dates in the dateadd() function. The following SQL query calculates the next month and previous month from the supplied datetime2 value.

SET NOCOUNT ON

Declare @inputdate datetime2= getdate()

SELECT GETDATE() AS Today,

DATEADD(MONTH,1,@inputdate) AS NextMonth,

DATEADD(MONTH,-1,@inputdate) AS PreviousMonth

Let’s see a few quick examples to help you understand the SQL DATEADD function.

Write a query to add 15 days to today’s date
Query:

1
2
3

SELECT DATEADD(dd, 15, getdate()); --Result: 2022-08-11 03:02:46.307
Write a query to Subtract 2 days from a specified date 2022-07-22
Query:

1
2
3

SELECT DATEADD(dd, -2,'2022-07-22') --Result: 2022-07-20 00:00:00.000
Modify the query to return only output as yyyy-mm-dd instead of yyyy-mm-dd hh:mm:ss.sss
Query:

1
2
3

SELECT cast(DATEADD(dd, -2,'2022-07-22') as date) –Result : 2022-07-20
Write a SQL query to return 20 years later from the specified date 2022-07-22 and return output in yyyy-mm-dd format.
Query:

1
2
3

SELECT cast(DATEADD(YY, 20,'2022-07-22') as date) --Result 2042-07-22
What will be the timestamp after 2 from the current date 2022-07-27 03:09:30.420
Query:

1
2
3

SELECT getdate() as currentdate,DATEADD(hh, 2,getdate())

Result: 2022-07-27 05:09:30.420
Add 1369569 years in the specified date 20220727
Query:

1
2
3

Query: SELECT DATEADD(year,1369569, '20220727');

Output: Adding a value to a ‘datetime’ column caused an overflow.

We cannot specify invalid or out-of-range values in the SQL SERVER DATEADD function. We get the following error message Output – Msg 517, Level 16, State 1, Line 1: Adding a value to a ‘datetime’ column caused an overflow.

Give an example of the DATEADD function using the RANK function

The DATEADD function works with the rank function in SQL Server as well. The following query uses Row_Number() function with the row number and system date time (SYSDATETIME() function) for the date parameter.

SELECT OrderID,

CustomerID,

DATEADD(day, ROW_NUMBER() OVER(

ORDER BY CustomerPurchaseOrderNumber), SYSDATETIME()) AS 'Row Number'

FROM [WideWorldImporters].[Sales].[Orders];

Write a SQL query that returns the DATEADD function output in a separate column
The following query calculates the newtimestamp and shows output in the[shipdate] column.

1
2
3
4
5
6

SELECT top 5 [ProductKey]
,[OrderDate]
,DateAdd(dd,3,Orderdate) as shipdate
FROM [AdventureWorksDW2019].[dbo].[FactInternetSales]

DATEDIFF Function in SQL Server

The DateDiff() function calculates the difference between the two input dates or time values. The function returns an integer value based on the unit specified in the datepart argument.

Syntax:

DATEDIFF ( datepart , startdate , enddate )

Datepart: The datepart is similar to the text we specified in the DATEADD() function.

Startdate and enddate can be in date, datetime, datetime2, datetimeoffset, smalldatatime, time data types.

The following query calculates the difference in the start date(2019-01-1) and end date (2022-01-01) for the year datepart.

SELECT DATEDIFF(year,'2019-01-01','2022-01-01');

We can change the datepart to return the difference in months or days like this.

SELECT DATEDIFF(year,'2019-01-01','2022-01-01') as 'Year',

DATEDIFF(MONTH,'2019-01-01','2022-01-01') as 'MONTH',

DATEDIFF(DAY,'2019-01-01','2022-01-01') as 'DAY';

If we reverse the start and end date, the DateDiff() function still works and returns negative integer values because the start date is greater than the end date.

The following T-SQL returns the time difference in hour and minute.

SELECT DATEDIFF(HOUR,'07:00:00.000','23:00:00.000') AS TimeInHours

, DATEDIFF(MINUTE,'07:00:00.000','23:30:00.000') AS TimeInMinutes

Similar to the DATEADD() function, we can use subqueries for specifying the start and end date values. In the following query, it calculates argument values with the following subqueries.

Start date: (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader)
End date: (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)

USE AdventureWorks2012;

SELECT DATEDIFF(day,

(SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),

(SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));

One useful scenario is calculating customer age based on the date of birth (DOB) stored in our database. It is a good idea to calculate the customer age at run time as it is not a constant value. We can use the DATEDIFF() function to check Age as per today’s date.

The following code declares a table variable, inserts sample customer data, and calculates age in years from the @customer table variable using the DATEDIFF() function.

Declare @customer table

(

id int,

[Name] varchar(20),

DOB date

)

Insert into @customer values(1,'Prem','1986-01-01')

Insert into @customer values(2,'Raju','1981-12-12')

Insert into @customer values(3,'Sam','1992-07-31')

Select

Name, Datediff(yy,DOB,getdate()) as Age from @customer

Let’s explore another example of the DATEDIFF() function in SQL Server. The following query retrieves different date parts such as the number of years, months, and quarters to see the results for product key 310.

SELECT DATEDIFF(YEAR, Orderdate, GETDATE()) No_Of_Years,

DATEDIFF(MONTH, Orderdate, GETDATE()) No_Of_Months,

DATEDIFF(quarter, Orderdate, GETDATE()) No_Of_Quarters,

DATEDIFF(WEEK, Orderdate, GETDATE()) Week_Of_Year,

DATEDIFF(DAYOFYEAR, Orderdate, GETDATE()) Day_Of_Year

FROM [AdventureWorksDW2019].[dbo].[FactInternetSales]

where productkey =310

We can also use the DATEDIFF() function in the where clause. For example, suppose you want to find your five years or older customers’ data. Here, we use Datediff() to return the date difference in years and where clause filters record values >=5.

SELECT *

FROM Customers

WHERE DATEDIFF(YY, OrderDate, GETDATE()) >=5;

DATEDIFF_BIG function in SQL Server

The DateDiff_BIG() function works similarly to the DATEDIFF() function, except that it returns the big int value from the specified datepart values.

To understand the difference between both functions, let’s execute the following code; it works fine and returns values until milliseconds.

SELECT

DATEDIFF(YEAR, GETDATE(), GETDATE()+20) AS [Year],

DATEDIFF(QUARTER , GETDATE(), GETDATE()+20) AS [Quarter],

DATEDIFF(MONTH, GETDATE(), GETDATE()+20) AS [nMonth],

DATEDIFF(DAYOFYEAR , GETDATE(), GETDATE()+20) AS [DayOfYear],

DATEDIFF(WEEK , GETDATE(), GETDATE()+20) AS [Week],

DATEDIFF(DAY, GETDATE(), GETDATE()+20) AS [Days],

DATEDIFF(HOUR , GETDATE(), GETDATE()+20) AS [Hour],

DATEDIFF(MINUTE, GETDATE(), GETDATE()+20) AS [Minute],

DATEDIFF(SECOND, GETDATE(), GETDATE()+20) AS [Sec],

DATEDIFF(MILLISECOND, GETDATE(), GETDATE()+20 ) AS [MilSec]

Let’s extend the time precision and try to get a microsecond value, and it raises the following error message:

To avoid this error, we can replace DATEDIFF() function with the DATEDIFF_BIG() function, as shown below.

SELECT

DATEDIFF_BIG(YEAR, GETDATE(), GETDATE()+20) AS [Year],

DATEDIFF_BIG(QUARTER , GETDATE(), GETDATE()+20) AS [Quarter],

DATEDIFF_BIG(MONTH, GETDATE(), GETDATE()+20) AS [nMonth],

DATEDIFF_BIG(DAYOFYEAR , GETDATE(), GETDATE()+20) AS [DayOfYear],

DATEDIFF_BIG(WEEK , GETDATE(), GETDATE()+20) AS [Week],

DATEDIFF_BIG(DAY, GETDATE(), GETDATE()+20) AS [Days],

DATEDIFF_BIG(HOUR , GETDATE(), GETDATE()+20) AS [Hour],

DATEDIFF_BIG(MINUTE, GETDATE(), GETDATE()+20) AS [Minute],

DATEDIFF_BIG(SECOND, GETDATE(), GETDATE()+20) AS [Sec],

DATEDIFF_BIG(MILLISECOND, GETDATE(), GETDATE()+20 ) AS [MilSec],

DATEDIFF_BIG(MICROSECOND, GETDATE(), GETDATE()+20 ) AS [MicroSec]

Use DATEADD and DATEDIFF() function together in SQL query

You can write the query in a SQL statement using the DATEADD and DATEDIFF() function. For example, suppose you have values below the start and end times.

StartTime: 2022-27-27 14:00:00

EndTime:2022-12-12 19:30:00

Firstly, to find the time difference, we will use DATEDIFF() function as below.

DECLARE @StartTime DATETIME= '2022-07-27 14:00:00'

Declare @EndTime DATETIME= '2022-12-12 19:30:00';

SELECT DATEDIFF(HOUR, @StartTime, @EndTime) as TimeDifference

The query returns 3317 hour difference in start time and end time.

Now, we want to use the time difference returned from the previous query as a parameter in the DATEADD function. The below query adds 3317 hours in the current timestamp returned from the getdate() function.

SELECT DATEADD(HH,3317,getdate()) AS ElapsedTime;

Let’s combine DATEADD and DATEDIFF functions in a single SQL statement as below.

DECLARE @StartTime DATETIME= '2022-07-27 14:00:00'

Declare @EndTime DATETIME= '2022-12-12 19:30:00';

SELECT DATEADD(hour,DATEDIFF(HOUR, @StartTime, @EndTime),getdate()) as ElapsedTime

Conclusion

This article explored the different ways to add or subtract dates in SQL Server using DATEADD, DATEDIFF, and DATEDIFF_BIG functions. I would advise you to be familiar with these functions and practice them to be familiar with their usage and outcome. You might need to use these functions frequently if your tables contain timestamp data.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023