SQL Server DATEADD() Function
FREE
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
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
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 |
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.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreAdding 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.
Turn Learning into Career Growth
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.
