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