SQL Server DATEADD() Function

The DATEADD() function in SQL is a powerful tool designed to perform date arithmetic, enabling the addition of a specified time interval to a date. This function is essential for manipulating dates within SQL queries, allowing for the calculation of future or past dates by adding or subtracting time units such as days, weeks, months, or years. Whether you're generating reports, scheduling future events, or analyzing historical data, DATEADD() offers a straightforward way to adjust dates according to your data processing needs.

Syntax

Parameter values

Parameter	Description
interval	Mandatory. Specifies the period to be added to a date.
number	Mandatory. The quantity of the specified interval to adjust the date by. This can be a positive number for future dates or a negative number for past dates.
date	Mandatory. The initial date to be altered.

Valid inputs for interval:

date_val	abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

SQL Server DATEADD() function Examples

Adding Seconds to the Given Date

In this example, we will be adding 20 seconds to 2022-5-25 23:59:50.

Code:

Output:

Explanation of the example:

In the above example, we are adding 20 seconds to the date 2022-5-25 23:59:50. The first parameter specifies that the value will be added to the seconds section of the given date. Upon the addition, we get the new value of the date, i.e. 2022-5-26 00:00:10.000.

Adding Days to the Given Date

Let's take the above case. This time we will be adding 3 days to 2022-5-25 23:59:50.

Code:

Output:

Explanation of the example:

In the above example, we are adding 3 days to the date 2022-5-25 23:59:50. The first parameter specifies that the value will be added to the days section of the given date. Upon the addition, we get the new value of the date, i.e. 2022-5-28 23:59:50.

Adding Months to the Given Date

Code:

Output:

Explanation of the example:

In the above example, we are adding 5 months to the date 2022-5-25 23:59:50. The first parameter specifies that the value will be added to the months section of the given date. Upon the addition, we get the new value of the date, i.e. 2022-10-28 23:59:50.

Calculating the Estimated Delivery Date of the Product

Suppose a grocery delivery company accepts orders online and it delivers the product within 2 days. We can use the DATEADD() function in SQL to get the current date and add 2 days and display the estimated delivery time.

Code:

Output:

Here the GETDATE() function will fetch the current date.

Learn More

In order to learn more about Time and date format in SQL, visit date-format-in-sql.

Conclusion

The DATEADD() function is used to add a date or time interval to a date.
The DATEADD() function in SQL takes three parameters.
We need to specify the part of the date that is incremented.
The DATEADD() in SQL returns a date value.