SQL Server provides various date and time functions that allow you to manipulate and perform calculations on date and time values. In this article, we will focus on two important functions, DATEADD and DATEDIFF, which are commonly used to subtract dates in SQL Server.

DATEADD Function

The DATEADD function allows you to add or subtract a specified number of date or time parts from a given date or time value. The syntax of the DATEADD function is as follows:

DATEADD(datepart, number, date)

The datepart parameter specifies the part of the date or time that you want to add or subtract, such as year, month, day, hour, minute, etc. The number parameter specifies the number of date or time parts to add or subtract. The date parameter is the starting date or time value.

Here are a few examples of using the DATEADD function to subtract dates:

SELECT DATEADD(month, 1, '2022-07-30'); -- Subtract 1 month from the date
SELECT DATEADD(year, 1, '2022-07-30'); -- Subtract 1 year from the date
SELECT DATEADD(dayofyear, 1, '2022-07-30'); -- Subtract 1 day of the year from the date
SELECT DATEADD(day, 1, '2022-07-30'); -- Subtract 1 day from the date
SELECT DATEADD(week, -1, '2022-07-30'); -- Subtract 1 week from the date
SELECT DATEADD(weekday, 1, '2022-07-30'); -- Subtract 1 weekday from the date
SELECT DATEADD(hour, 1, '2022-07-30'); -- Subtract 1 hour from the date
SELECT DATEADD(minute, 1, '2022-07-30'); -- Subtract 1 minute from the date
SELECT DATEADD(second, 1, '2022-07-30'); -- Subtract 1 second from the date

Note that to subtract a date, you need to use a negative value for the number parameter.

DATEDIFF Function

The DATEDIFF function is used to calculate the difference between two date or time values. It returns the number of specified date or time parts crossed between the start date and end date. The syntax of the DATEDIFF function is as follows:

DATEDIFF(datepart, startdate, enddate)

The datepart parameter specifies the part of the date or time that you want to calculate the difference in, such as year, month, day, hour, minute, etc. The startdate and enddate parameters are the two dates or times that you want to compare.

Here are a few examples of using the DATEDIFF function to subtract dates:

SELECT DATEDIFF(year, '2022-09-01', '2023-10-02'); -- Subtract the number of years between the dates
SELECT DATEDIFF(quarter, '2022-09-01', '2023-10-02'); -- Subtract the number of quarters between the dates
SELECT DATEDIFF(month, '2022-09-01', '2023-10-02'); -- Subtract the number of months between the dates
SELECT DATEDIFF(dayofyear, '2022-09-01', '2023-10-02'); -- Subtract the number of days of the year between the dates
SELECT DATEDIFF(day, '2022-09-01', '2023-10-02'); -- Subtract the number of days between the dates
SELECT DATEDIFF(week, '2023-10-02', '2022-09-01'); -- Subtract the number of weeks between the dates
SELECT DATEDIFF(hour, '2022-09-01', '2023-10-02'); -- Subtract the number of hours between the dates
SELECT DATEDIFF(minute, '2022-09-01', '2023-10-02'); -- Subtract the number of minutes between the dates
SELECT DATEDIFF(second, '2022-09-01', '2023-10-02'); -- Subtract the number of seconds between the dates

It’s important to note that the DATEDIFF function returns an integer value. If the difference between the dates exceeds the range of an integer, you may encounter an error. In such cases, you can use the DATEDIFF_BIG function, which supports larger date differences.

In conclusion, the DATEADD and DATEDIFF functions are powerful tools in SQL Server for subtracting dates and calculating the difference between dates. By understanding how to use these functions, you can perform various date calculations and manipulations in your SQL queries.