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:

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

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:

OrderQuantity	Price	OrderDate	PaymentDate
100	1.58	2017-08-18	2017-08-20
120	1.61	2018-03-23	2018-03-28
125	1.78	2018-09-12	2018-09-22
50	1.80	2019-01-16	2019-01-17
200	1.72	2020-02-06	2020-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:

OrderQuantity	Price	OrderDate	PaymentDate	SettlementDays
100	1.58	2017-08-18	2017-08-20	2
120	1.61	2018-03-23	2018-03-28	5
125	1.78	2018-09-12	2018-09-22	10
50	1.80	2019-01-16	2019-01-17	1
200	1.72	2020-02-06	2020-02-08	2

❮ SQL Server Functions