The DATEADD function is used in SQL to add a specific interval to a date. The function takes three arguments:
1. datepart: This specifies the part of the date that you want to add (e.g., year, month, day, hour, minute, etc.).
2. number: This is the integer value that you want to add to the datepart. It can be a positive or negative number, depending on whether you want to move the date forward or backward.
3. date: This is the original date to which you want to add the interval.
Here’s the syntax for the DATEADD function:
DATEADD(datepart, number, date)
Example Usage
1. Adding Days:
To add 10 days to a specific date:
SELECT DATEADD(DAY, 10, '2023-01-01') AS NewDate;
Output:
NewDate
----------
2023-01-11
2. Adding Months:
To add 2 months to a specific date:
SELECT DATEADD(MONTH, 2, '2023-01-01') AS NewDate;
Output:
NewDate
----------
2023-03-01
3. Adding Years:
To add 5 years to a specific date:
SELECT DATEADD(YEAR, 5, '2023-01-01') AS NewDate;
Output:
NewDate
----------
2028-01-01
4. Adding Negative Intervals:
To subtract 3 days from a specific date:
SELECT DATEADD(DAY, -3, '2023-01-01') AS NewDate;
Output:
NewDate
----------
2022-12-29
Common Dateparts
Some common datepart values you can use:
- YEAR
- QUARTER
- MONTH
- DAY
- WEEK
- HOUR
- MINUTE
- SECOND
Note
Make sure that the date format you use is compatible with your SQL database's date format settings. Different SQL systems might have slightly different syntaxes or implementations, so check the documentation specific to the system you're using (e.g., SQL Server, MySQL, PostgreSQL).