Menu

SQL Server DATEDIFF() Function

Updated on

DATEDIFF(datepart, startdate, enddate) returns the signed number of specified date-part boundaries crossed between two date/time values. It does not measure a fractional elapsed duration: two timestamps one hundred nanoseconds apart can produce 1 if they straddle a year, month, or day boundary. See Microsoft’s DATEDIFF documentation.

Syntax

DATEDIFF(datepart, startdate, enddate)

Parameters

  • datepart: A supported literal such as day, month, hour, or second. Do not quote it or pass it as a variable.
  • startdate: The value at which counting begins.
  • enddate: The value at which counting ends. The result is negative when enddate is earlier than startdate.

DATEDIFF() returns an int; use DATEDIFF_BIG() when the expected boundary count may exceed that range.

Examples

Count day boundaries

This pair of values crosses 30 day boundaries:

SELECT DATEDIFF(day, '2022-01-01', '2022-01-31');

Result:

30

A small elapsed interval can cross a boundary

These values are only 100 nanoseconds apart, but they cross midnight, so DATEDIFF(day, ...) returns 1:

SELECT DATEDIFF(
    day,
    '2026-04-20T23:59:59.9999999',
    '2026-04-21T00:00:00.0000000'
);

Use a smaller datepart when you need finer boundary counts, and use an explicit numeric calculation when the requirement is exact elapsed time. DATEDIFF() is often useful for filtering intervals, but its boundary-count behavior should match the business rule.