SQL Server DATEDIFF_BIG() Function

DATEDIFF_BIG(datepart, startdate, enddate) returns a signed bigint count of the specified date-part boundaries crossed between two date/time values. Like DATEDIFF(), it counts boundaries rather than fractional elapsed duration. DATEDIFF_BIG() is available in SQL Server 2016 and later. See Microsoft’s DATEDIFF_BIG reference.

Syntax

DATEDIFF_BIG(datepart, startdate, enddate)

datepart must be a supported literal such as day, hour, millisecond, or nanosecond; it cannot be a variable or quoted string. The start and end values can be date/time expressions, including date, datetime2, datetimeoffset, smalldatetime, or time.

Use a bigint result for large intervals

DATEDIFF() returns an int and can overflow on large intervals at fine date-part precision. DATEDIFF_BIG() returns a bigint for the same boundary-count calculation:

DECLARE @startdate datetime2 = '1900-01-01T00:00:00';
DECLARE @enddate datetime2 = '2026-09-28T00:00:00';

SELECT DATEDIFF_BIG(millisecond, @startdate, @enddate) AS milliseconds;

The result is the number of millisecond boundaries crossed. At nanosecond precision, DATEDIFF_BIG() can still overflow for intervals longer than roughly 292 years. Choose the date part that matches the required resolution and range.

For the int-returning version and examples of boundary behavior, see DATEDIFF().