DATEDIFF_BIG returns the number of dateparts between a start and end date.
The returned value is a big integer -- useful for seconds, microseconds, etc.
Dateparts include year, month, day, minute, seconds, 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_BIG(year, '1996-09-25', '2021-09-25') AS 'Year Difference'
| Year 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_BIG(month, '2021-5-22', GETDATE()) AS 'Elapsed months'
| Elapsed Months |
|---|
| 30 |
Syntax of the DATEDIFF_BIG function .
DATEDIFF_BIG(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_BIG(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_BIG(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 |
 |
|||
