Hello and welcome to this guide on the SQL Server DateAdd function! If you’re a developer or a data analyst, working with dates and times is a common task. Thankfully, SQL Server provides several built-in functions that make this task relatively easy. One of these functions is the DateAdd function, which allows you to add or subtract a specified time interval to or from a datetime value.
Introduction to SQL Server DateAdd Function
The DateAdd function is used to add or subtract a specified time interval to or from a datetime value. The syntax of the DateAdd function is as follows:
| Parameter | Description |
|---|---|
| DatePart | The component of the datetime value that you want to add or subtract the amount to. This can be one of the following values: year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, or nanosecond. |
| Number | The number of intervals to add or subtract. This can be a positive or negative integer value. |
| Date | The datetime value that you want to modify. |
Let’s take a closer look at each of these parameters.
DatePart
The DatePart parameter specifies the component of the datetime value that you want to add or subtract the amount to. This can be one of the following values:
| Value | Description |
|---|---|
| year | Add or subtract a number of years. |
| quarter | Add or subtract a number of quarters. |
| month | Add or subtract a number of months. |
| dayofyear | Add or subtract a number of days from the start of the year. |
| day | Add or subtract a number of days. |
| week | Add or subtract a number of weeks. |
| weekday | Add or subtract a number of weekdays (Monday to Friday). |
| hour | Add or subtract a number of hours. |
| minute | Add or subtract a number of minutes. |
| second | Add or subtract a number of seconds. |
| millisecond | Add or subtract a number of milliseconds. |
| microsecond | Add or subtract a number of microseconds. |
| nanosecond | Add or subtract a number of nanoseconds. |
For example, if you want to add 3 months to a datetime value, you would use the DatePart parameter with the value of ‘month’.
Number
The Number parameter specifies the number of intervals to add or subtract. This can be a positive or negative integer value.
For example, if you want to add 3 months to a datetime value, you would use the Number parameter with the value of 3.
Date
The Date parameter specifies the datetime value that you want to modify.
For example, if you have a datetime value of ‘2021-01-01 00:00:00’ and you want to add 3 months to it, you would use the Date parameter with the value of ‘2021-01-01 00:00:00’.
Examples of SQL Server DateAdd Function
Let’s take a look at some examples of how to use the DateAdd function in SQL Server.
Example 1: Adding Days to a Date
If you want to add a number of days to a datetime value, you can use the DatePart parameter with the value of ‘day’, and the Number parameter with the number of days you want to add.
For example, if you want to add 7 days to a datetime value of ‘2021-01-01 00:00:00’, you would use the following syntax:
SELECT DATEADD(day, 7, '2021-01-01 00:00:00')
The result of this query would be:
| Result |
|---|
| 2021-01-08 00:00:00.000 |
Example 2: Subtracting Months from a Date
If you want to subtract a number of months from a datetime value, you can use the DatePart parameter with the value of ‘month’, and the Number parameter with the number of months you want to subtract (as a negative integer).
For example, if you want to subtract 3 months from a datetime value of ‘2021-01-01 00:00:00’, you would use the following syntax:
SELECT DATEADD(month, -3, '2021-01-01 00:00:00')
The result of this query would be:
| Result |
|---|
| 2020-10-01 00:00:00.000 |
Example 3: Adding Hours to a Date
If you want to add a number of hours to a datetime value, you can use the DatePart parameter with the value of ‘hour’, and the Number parameter with the number of hours you want to add.
For example, if you want to add 5 hours to a datetime value of ‘2021-01-01 00:00:00’, you would use the following syntax:
SELECT DATEADD(hour, 5, '2021-01-01 00:00:00')
The result of this query would be:
| Result |
|---|
| 2021-01-01 05:00:00.000 |
FAQs
What is the purpose of the DateAdd function?
The DateAdd function is used to add or subtract a specified time interval to or from a datetime value. This is useful for tasks such as calculating dates in the future or past.
What are the parameters of the DateAdd function?
The DateAdd function has three parameters: DatePart, Number, and Date. DatePart specifies the component of the datetime value that you want to add or subtract the amount to. Number specifies the number of intervals to add or subtract. Date specifies the datetime value that you want to modify.
What are the values that can be specified for the DatePart parameter?
The DatePart parameter can be one of the following values: year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, or nanosecond.
Can the Number parameter be a negative value?
Yes, the Number parameter can be a positive or negative integer value.
What is the syntax for using the DateAdd function?
The syntax for using the DateAdd function is:
DATEADD(DatePart, Number, Date)
Where DatePart is the component of the datetime value that you want to add or subtract the amount to, Number is the number of intervals to add or subtract, and Date is the datetime value that you want to modify.
Conclusion
The SQL Server DateAdd function is a powerful tool for working with dates and times in SQL Server. By using this function, you can easily add or subtract a specified time interval to or from a datetime value, making tasks such as calculating dates in the future or past much easier. We hope this guide has been helpful in understanding the DateAdd function, and we encourage you to experiment with it in your own SQL Server projects.