The DATEADD function is commonly used in SQL to add a specified interval to a date. The syntax for the DATEADD function is generally as follows:
DATEADD(datepart, number, date)
Parameters:
- datepart: This specifies the part of the date to which you want to add an interval. Common options include:
- year, yy, yyyy for years
- quarter, qq, q for quarters
- month, mm, m for months
- day, dd, d for days
- week, wk, ww for weeks
- hour, hh for hours
- minute, mi, n for minutes
- second, ss, s for seconds
- number: This is the value that you want to add. Use a positive number to add and a negative number to subtract.
- date: This is the date to which the interval will be added.
Example:
Here is an example of using DATEADD to add a month to the current date:
SELECT DATEADD(month, 1, GETDATE()) AS NextMonth
In this example:
- The datepart is month.
- The number is 1 (which means we are adding one month).
- The date is GETDATE(), which retrieves the current date and time.
Another Example:
Suppose you want to add 7 days to a specific date (e.g., ‘2023-10-01’):
SELECT DATEADD(day, 7, '2023-10-01') AS NewDate
In this case:
- The result will be ‘2023-10-08’, since we added 7 days to October 1, 2023.
You can use DATEADD in various scenarios, such as calculating future dates, creating time-based filters in queries, or manipulating date dimensions in reports.