How to use the DateAdd() function in SQL

Date manipulation is a common task nowadays because it allows us to analyze and evaluate time-related data, which is important in many date-time-related tasks (e,g., in payment systems). There are multiple date functions for different purposes in SQL and DateAdd() is one of them. In this Answer, we will discuss using the DateAdd() function with code examples.

The DateAdd() function

The DateAdd() function is an SQL server date function that allows us to add or subtract specific time interval from the given date. After adding or subtracting a specific interval, it will return the modified date. This can be useful in generating invoice's due dates or setting reminders for different events.

Syntax

The syntax of the DateAdd() function is given below.

DATEADD(datepart , number , date)

Let's discuss the parameters in detail:

  • datepart: This represents a specific part of the date to which this function adds/subtracts the number. The possible values can be:

The datepart Parameter

Format

year

yy, yyyy

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: The number can be a positive or negative integer. If the number is positive, it adds to the datepart to calculate the future date, and in the case of a negative number, it subtracts from datepart to determine the past date.

  • date: This is a date which is to be modified. This can be of multiple data types, as given below:

    • date: This defines a date without a time component.

    • datetime: This represents a combination of date and time, with optional fractional seconds and timezone offset.

    • datetimeoffset: This represents a combination of date and time with timezone offsetThe difference between a particular time zone and Coordinated Universal Time (UTC) expressed in hours and minutes is known as a time zone offset. It shows how much time must be added or removed from UTC in order to get the local time in a specific time zone..

    • datetime2: This is a more precise version of the datetime data type, supporting larger fractional seconds and a broader range of dates.

    • smalldatetime: This is similar to datetime but with a smaller range and precision

    • time: This defines a time of day without a date component.

It applies to various Microsoft data platforms, including SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, Analytics Platform System (PDW), and SQL analytics endpoint in Microsoft Fabric or Warehouse in Microsoft Fabric.

Code examples

Using this function, we can add to or subtract from a given date-time. For example, we can see what the date will be after 5 days. Here is an example of doing this task:

-- Date after 5 days
SELECT DATEADD(day, 5, '1997-10-03 23:59:59');
Adding five days in the given date

On line 2, we add 5 days in the given date using the DATEADD function and SELECT statement is used to retrieve the result of the DATEADD function.

To efficiently manipulate the time, we'll specify datepart parameters like hour, minute, second, etc., as demonstrated below:

-- Add 1 second
SELECT DATEADD(second, 1, '1997-10-03 23:59:59');

-- Add 1 hour
SELECT DATEADD(hour, 1, '1997-10-03 22:05:05');

-- Add 1 minute
SELECT DATEADD(minute, 1, '1997-10-03 22:05:05');
Updating time using the DATEADD() function

To get past dates, we need to mention negative values to subtract datepart parameter from date parameter as given below:

-- Substract 3 years
SELECT DATEADD(year, -3, '1997-10-03 23:59:59');
Getting past dates using the DATEADD() function

We must be careful when utilizing DATEADD function or accepting input from users, as sometimes the provided input date is invalid or the specified number parameter exceeds the range of the integer or makes the date outside the valid range. Here are some examples where errors may occur:

-- Usecase: Exceeds the range of the integer
-- Error: Arithmetic overflow error converting expression to data type int
SELECT DATEADD(DAY, 2147483648, '1997-10-03 23:59:59');

-- Usecase: Invalid 'date' parameter
-- Error: Out of range value (Due to invalid month in date part)
SELECT DATEADD(month, 1, '1997-13-03 22:05:05');
Code example for Invalid or out-of-range date

We can use the DateAdd() function in these SQL clauses: GROUP BY, ORDER BY, HAVING, WHERE and SELECT <list>.

Conclusion

In this Answer, we understand how to use the DATEADD() function in SQL. It allows us to manipulate date and time data efficiently. By understanding its syntax and usage, we can effectively utilize this function to perform various date calculations, ensuring accurate and effective database operations.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved