The SQL DATEADD function is used to add a specified number of intervals to a given date, and returns a new date. The function is commonly used in SQL Server and other relational database management systems.
Syntax
The syntax of the DATEADD function is as follows:
DATEADD(interval, number, date)
Here, the “interval” parameter specifies the type of time interval to add, such as year, month, day, hour, minute, second, etc. The “number” parameter specifies the number of intervals to add, and can be positive or negative. Finally, the “date” parameter specifies the starting date to which the intervals will be added.
Examples
For example, to add one month to a given date, the following SQL query can be used:
SELECT DATEADD(month, 1, '2022-03-15');
This will return the date ‘2022-04-15’, which is one month after the given date.
Similarly, to subtract two days from a given date, the following SQL query can be used:
SELECT DATEADD(day, -2, '2022-05-10');
This will return the date ‘2022-05-08’, which is two days before the given date.
In addition to adding or subtracting intervals from a date, the DATEADD function can also be used to perform more complex calculations, such as adding or subtracting multiple intervals. For example, to add one year, two months, and three days to a given date, the following SQL query can be used:
SELECT DATEADD(day, 3, DATEADD(month, 2, DATEADD(year, 1, '2022-01-01')));
This will return the date ‘2023-03-04’, which is one year, two months, and three days after the given date.
In conclusion, the SQL DATEADD function is a powerful tool for performing date calculations in SQL queries. It allows developers to add or subtract intervals from a given date, and to perform more complex calculations involving multiple intervals.