SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server DATEDIFF() Function



The SQL Server (Transact-SQL) DATEDIFF() function returns the difference between two date values, based on the specified unit.

Syntax

DATEDIFF(datepart, date1, date2)

Parameters

datepart

Required. Specify the unit type of the result. It can be one of the following values:

datepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
date1 Required. Specify the first date value to calculate the difference (date1 - date2).
date2 Required. Specify the second date value to calculate the difference (date1 - date2).

Return Value

Returns the difference between two date values expressed in specified unit.

Example 1:

The example below shows the usage of DATEDIFF() function.

SELECT DATEDIFF(year, '2019-10-25', '2024-10-15');
Result: 5

SELECT DATEDIFF(yyyy, '2019-10-25', '2024-10-15');
Result: 5

SELECT DATEDIFF(yy, '2019-10-25', '2024-10-15');
Result: 5

SELECT DATEDIFF(quarter, '2019-10-25', '2024-10-15');
Result: 20

SELECT DATEDIFF(month, '2019-10-25', '2024-10-15');
Result: 60

SELECT DATEDIFF(day, '2019-10-15 10:20:25', '2019-10-25 12:40:45');
Result: 10

SELECT DATEDIFF(hour, '2019-10-15 10:20:25', '2019-10-25 12:40:45');
Result: 242

SELECT DATEDIFF(minute, '2019-10-15 10:20:25', '2019-10-25 12:40:45');
Result: 14540

SELECT DATEDIFF(second, '2019-10-15 10:20:25', '2019-10-25 12:40:45');
Result: 872420

Example 2:

Consider a database table called Orders with the following records:

OrderQuantityPriceOrderDatePaymentDate
1001.582017-08-182017-08-20
1201.612018-03-232018-03-28
1251.782018-09-122018-09-22
501.802019-01-162019-01-17
2001.722020-02-062020-02-08

To calculate the number of days taken to settle the deal, the following query can be used:

SELECT *, DATEDIFF(day, PaymentDate, OrderDate) AS SettlementDays
FROM Orders;

This will produce a result similar to:

OrderQuantityPriceOrderDatePaymentDateSettlementDays
1001.582017-08-182017-08-202
1201.612018-03-232018-03-285
1251.782018-09-122018-09-2210
501.802019-01-162019-01-171
2001.722020-02-062020-02-082

❮ SQL Server Functions