The DATEADD function in SQL is used to add or subtract a specified time interval from a given date. This function can be particularly useful when you need to calculate dates that are a certain number of days, months, years, etc., from a given datetime value.
Syntax
The basic syntax of the DATEADD function is as follows:
DATEADD(datepart, number, date)
- datepart: Specifies the part of the date to which the interval will be added (e.g., year, quarter, month, day, hour, minute, second).
- number: The number of intervals you want to add (positive value) or subtract (negative value).
- date: The date to which you want to add the interval.
Common Date Parts
Here are some common date parts you might use with DATEADD:
- YEAR or YY: Year
- MONTH or MM: Month
- DAY or DD: Day
- HOUR or HH: Hour
- MINUTE or MI: Minute
- SECOND or SS: Second
Example Usage
1. Adding Days:
SELECT DATEADD(DAY, 7, '2023-10-01') AS NewDate;
This will return the date that is 7 days after October 1, 2023, which is October 8, 2023.
2. Subtracting Months:
SELECT DATEADD(MONTH, -2, '2023-10-01') AS NewDate;
This will return the date that is 2 months before October 1, 2023, which is August 1, 2023.
3. Adding Years:
SELECT DATEADD(YEAR, 1, '2023-10-01') AS NewDate;
This will return the date that is 1 year after October 1, 2023, which is October 1, 2024.
4. Combining with Current Date:
You can also use GETDATE() to get the current date and time:
SELECT DATEADD(HOUR, 5, GETDATE()) AS NewTime;
This would return the current date and time plus 5 hours.
Notes
- The number can be negative to subtract from the date.
- Be mindful of the data types; DATEADD typically returns a value of the same type as the input date.
Using DATEADD effectively helps in managing date arithmetic in SQL, enhancing the functionality of your queries.