Rajendra Gupta
DATEDIFF_BIG function

How to add or subtract dates in SQL Server

December 15, 2022 by

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

SQL DATEADD function

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

Subtract dates in SQL Server

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.

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.

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.

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

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.

DATEADD function with negative values

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

  1. Write a query to add 15 days to today’s date

    Query:

  2. Write a query to Subtract 2 days from a specified date 2022-07-22

    Query:

  3. Modify the query to return only output as yyyy-mm-dd instead of yyyy-mm-dd hh:mm:ss.sss

    Query:

  4. 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:

  5. What will be the timestamp after 2 from the current date 2022-07-27 03:09:30.420

    Query:

    Result: 2022-07-27 05:09:30.420

  6. Add 1369569 years in the specified date 20220727

    Query:

    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.

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

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

    SQL query that returns the DATEADD function

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.

SQL DATEDIFF function

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

DATEFIFF function with different date part

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.

Negative integer and datediff function

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

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)

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.

add or subtract dates in SQL Server

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.

DATEDIFF with year, month, week values

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.

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.

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

DATEDIFF_BIG function in SQL Server

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

DATEDIFF_BIG function

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.

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

DATEADD and DATEDIFF() function together in SQL query

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.

DATEADD and DATEDIFF for subtract dates in SQL Server

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

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views