SQL Server provides several built-in functions to calculate the difference between dates. One of the most commonly used functions is the DATEDIFF function. DATEDIFF is an date function and returns the count of the specified date_part boundaries crossed between twoo specified dates: start_date and end_date.

Syntax

The syntax for the DATEDIFF function is as follows:

DATEDIFF(interval, startdate, enddate)

The "interval" parameter specifies the unit of time to use for the calculation, such as year, month, day, hour, minute, or second. The "startdate" and "enddate" parameters are the two dates for which you want to calculate the difference.

Example

Here's an example that calculates the number of days between two dates:

SELECT DATEDIFF(day, '2022-01-01', '2022-01-15') AS DateDiff;

In this example, we're using the "day" interval to calculate the difference between January 1, 2022 and January 15, 2022. The result will be 14, since there are 14 days between those two dates.

You can also use the DATEDIFF function to calculate the difference between two datetime values, as shown in the following example:

SELECT DATEDIFF(second, '2022-01-01 10:00:00', '2022-01-01 10:00:30') AS DateDiff;

In this example, we're using the "second" interval to calculate the difference between January 1, 2022 at 10:00:00 and January 1, 2022 at 10:00:30. The result will be 30, since there are 30 seconds between those two datetimes.

SELECT DATEDIFF(year, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Year_diff,
DATEDIFF(month, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Months_diff,
DATEDIFF(day, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Days_diff;

In addition to the DATEDIFF function, SQL Server also provides other date and time functions, such as DATEADD, DATEPART, and CONVERT, that can be used in combination to perform more complex date calculations.