SQL DATEDIFF_BIG Function

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.

Example

#

This example returns the number of years between 2 dates.

SELECT DATEDIFF_BIG(year, '1996-09-25', '2021-09-25') AS 'Year Difference'
Result:  1 record
Year Difference
25

Using DATEDIFF_BIG

#

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'
Result:  1 record
Elapsed Months
6

Syntax

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

More Examples

DATEDIFF with DAY

Return the number of days between two dates.
SELECT DATEDIFF_BIG(day, '2021-09-01', '2021-12-25') AS 'Days Before Christmas'
Result:  1 record
Days Before Christmas
115

DATEDIFF in WHERE

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all orders that are 100 days or older.
SELECT FirstName, LastName, OrderNumber, OrderDate
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
 WHERE DATEDIFF_BIG(day, O.OrderDate, GETDATE()) >= 100
Result:  830 records
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

You may also like



Guides


vsn 3.1