DATEDIFF returns the number of dateparts between a start and end date.
Dateparts include year, month, day, minute, and others (values listed below).
The returned number of dateparts can be positive or negative.
This example returns the number of years between 2 dates.
SELECT DATEDIFF(year, '1996-09-25', '2021-09-25') AS 'Years Difference'
| Years Difference |
|---|
| 25 |
A common use is to get the elapsed time from the current date.
In this example the difference is in months.
SELECT DATEDIFF(month, '2021-5-22', GETDATE()) AS 'Elapsed months'
| Elapsed Months |
|---|
| 31 |
Syntax of the DATEDIFF function .
DATEDIFF(date_part, start_date, end_date)
date_part -- the part of date measurement (datepart values are listed below).
start_date -- a start date, datetime, datetimeoffset, datetime2, smalldatetime, or time.
end_date -- the same as start_date but this one specifies the end date.
These are valid date_part values and possible abbreviations
| Date part | Abbreviation |
|---|---|
year |
yyyy or yy |
quarter |
qq or q |
month |
mm or m |
dayofyear |
dy or y |
day |
dd or d |
week |
wk or ww |
weekday |
dw |
hour |
hh |
minute |
mi or n |
second |
ss or s |
millisecond |
ms |
microsecond |
mcs |
nanosecond |
ns |
SELECT DATEDIFF(day, '2021-09-01', '2021-12-25') AS 'Days Before Christmas'
| Days Before Christmas |
|---|
| 115 |
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
SELECT FirstName, LastName, OrderNumber, OrderDate
FROM [Order] O
JOIN Customer C ON O.CustomerId = C.Id
WHERE DATEDIFF(day, O.OrderDate, GETDATE()) >= 100
| FirstName | LastName | OrderNumber | OrderDate |
|---|---|---|---|
| Paul | Henriot | 542378 | 2012-07-04 00:00:00.000 |
| Karin | Josephs | 542379 | 2012-07-05 00:00:00.000 |
| Mario | Pontes | 542380 | 2012-07-08 00:00:00.000 |
| Mary | Saveley | 542381 | 2012-07-08 00:00:00.000 |
| Pascale | Cartrain | 542382 | 2012-07-09 00:00:00.000 |
 |
|||
