DATEADD Function in SQL Server

In this SQL Server tutorial, I will explain the DATEADD function in SQL Server, which allows us to manipulate the date part.

This function is very useful in situations where you need to quickly compute the age of employees, schedule events, etc.

First, you will understand its definition with syntax, and then, with examples, you will understand how to use it to manipulate the date and time value.

Afterwards, you will use the DATEADD function in SQL Server on the table; as you are a beginner or experienced database developer, you always work with table data, and a situation may arise where you need to perform some operation on the date and time value; you can use it there.

Finally, you will understand how to use the variable within the DATEADD function in SQL Server.

DATEADD Function in SQL Server

The DATEADD function in SQL Server adds the date and time unit to the specified date and returns the new datetime value.

In other words, if you have a datetime value and you want to add the one day, year, month, or second to that datetime value, then it can be added by the DATEADD() function.

For example, if you add 1 year to this date, ‘2024-16-01’, it becomes ‘2025-16-01’; similarly, you can use the DATEADD() function to add the year, month, hours, seconds, etc, to a specific date and time value.

Now you understand the concept behind the DATEADD function in SQL Server, the syntax is given below.

DATEADD (datepart , number , date )

Where,

• DATEADD(): The function adds the datetime interval to the given date.
• datepart: This is the specific part of the date that you want to add to the date; it can be year, month, day, hours, minutes, second, etc. It means which part of the date you want to increase.
• number: It is the integer number (positive or negative) that defines how much you want to increase the specific part of the date; let’s say you want to increase 1 year, 2 months, 5 seconds, etc.

While specifying, you can use the following depart within the DATEADD() function.

Now, understand with an example, so you have the date value ‘2024-01-26’, and need to increase the month part by 2 of this date. For that, run the query below.

SELECT DATEADD(month,2, '2024-01-26') AS AddingMonth;

The DATEADD() function added two months to the date ‘2024-01-26’ and returns a new date value, ‘2024-03-26’, that you can see in the output.

In the query part, DATEADD(month,2, ‘2024-01-26’) AS AddingMonth.

Here, month represents that you want to add the month part of the date, 2 represents that you want to increase the month by 2, and the ‘2024-01-26’ is the date whose month part is increased by 2.

Let’s take one more example, where we will increase the minutes part of the datetime value. For example, you have a datetime value ‘2024-01-28 12:10:03’. Execute the query below to increase the minutes part.

SELECT DATEADD(minute,15, '2024-01-28 12:10:03') AS AddedMinutes;

The result contains the new datetime ‘2024-01-28 12:25:03’. Now look at the minute part of this datetime value, which is increased by 15 minutes using the DATEADD() function.

In this query part DATEADD(minute,15, ‘2024-01-28 12:10:03’), the minute part of the date value ‘2024-01-28 12:10:03’ is increased by 15, here the date part is minute, the number is 15, and the date is the ‘2024-01-28 12:10:03’.

• In the same way, you can use any date part, such as day, hour, nanoseconds, or seconds, and then specify the number (which is how much the date part value you want to increase) within the DATEADD() function. And this function will increase a specific part of the date.

This is how to use the DATEADD function in SQL Server to add the month, year, minutes, etc, to the specified date.

DATEADD Function in SQL Server on Table Column

Here, you will understand how to use the DATEADD() function on the table column; in the real world, you usually interact with the table, so a situation can arise where you must add the interval of the datetime value to the table containing the date values.

For example, you can access the Subscription table, which contains the service name, such as Netflix or Amazon, and its subscription starting date, shown below.

So, you must compute each subscription service’s one-month based expiry date from the starting date. You can use the DATEADD() function and execute the query below to compute the expiry date.

SELECT
    Service,
	StartDate,
	DATEADD(day, 30, StartDate) AS ExpiryDate
FROM Subscriptions;

From the output, the expiry date based on one month is computed from the starting date of the subscription. You can see the result set in the ExpiryDate column. For example, the starting date of Netflix is 2024-01-01 00:00:00.000, and the expiry date is 2024-01-31 00:00:00.000.

Let’s understand the query part DATEADD(day, 30, StartDate). Here, the date part is the day, which means this part of the date will increment, and then 30 is the number, which means increasing the day part of the date by 30; the startDate is the column which contains the starting date of the subscription of each service.

As a result, the query computed the expiry date for the service subscription. This is how to use the DATEADD function in SQL Server.

Using User-Defined Variable in DATEADD Function in SQL Server

You can even pass the user-defined variable containing the number and date to the DATEADD function in SQL Server.

For example, use the query below to define the variables @number and @date with the following values: 3 and ‘2024-01-20’.

DECLARE @number INT = 3;
DECLARE @date DATE = '2024-01-20'

Use the query below to add the 3 years to that date.

SELECT DATEADD(year, @number, @date) AS AddedYear;

From the above output, 3 years of the interval is added to the date ‘2024-01-20’, but we have passed the variables @number and @date to the DATEADD() function.

As we haven’t passed the value directly to the DATEADD() function, we instead passed the value through a variable. But remember, if you pass the date part value through a variable, which is the year in this case, it raises an error.

If you want to check, define one more variable and assign a value year to it, then pass the variable to the DATEADD function in SQL Server and see what happens.

This is how to use the variable in the DATEADD function in SQL Server to add the date interval to a specified date.

Conclusion

In this SQL Server tutorial, you learned how to add the datetime interval, such as a month, year, etc, to the given date. Also, you have used the DATEADD() function to compute the subscription’s expiry date related to the service.

Additionally, You learned how to use a variable with the DATEADD function in SQL Server by the end.

You may like to read:

• DATEDIFF Function in SQL Server
• DATENAME Function of SQL Server
• SQL Server DATETRUNC Function

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.