SQL Server DATEDIFF: A Comprehensive Guide to Calculating Date and Time Differences

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

In SQL Server, accurately calculating the difference between two dates or times is a common requirement for various database operations, reporting, and data analysis tasks. The DATEDIFF function in Transact-SQL (T-SQL) is a powerful tool designed precisely for this purpose. It allows you to determine the count of specified datepart boundaries crossed between a startdate and an enddate. This article provides a comprehensive guide to understanding and effectively using the Sql Server Datediff function.

For scenarios involving potentially larger date differences that might exceed the range of the INT data type, consider using DATEDIFF_BIG. For a broader understanding of date and time functionalities in SQL Server, refer to the overview of Date and time data types and functions.

Transact-SQL syntax conventions

Syntax

The syntax for the DATEDIFF function is straightforward:

DATEDIFF ( datepart , startdate , enddate )

Arguments

The DATEDIFF function requires three arguments: datepart, startdate, and enddate. Let’s explore each of these in detail.

datepart

The datepart argument is crucial as it defines the unit in which DATEDIFF will calculate and report the difference between the provided startdate and enddate. Think of datepart as specifying the granularity of the date difference you want to measure. Common examples include month to calculate the difference in months, or second to calculate the difference in seconds.

It’s important to note that the datepart value cannot be supplied using a variable or as a quoted string like 'month'. It must be one of the predefined keywords recognized by SQL Server.

The following table lists all valid datepart values accepted by the DATEDIFF function. You can use either the full name of the datepart (e.g., year) or any of its listed abbreviations (e.g., yy, yyyy). Both the full name and its abbreviations will yield the same result.

datepart name	datepart abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

startdate

The startdate argument specifies the starting date for the calculation. It can be any expression that resolves to one of the following SQL Server data types:

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

For clarity and to avoid potential issues with year 2000 compliance and ambiguity, it is strongly recommended to always use four-digit years when specifying dates. For more information on handling two-digit year values, refer to Server configuration: two digit year cutoff.

enddate

The enddate argument specifies the ending date for the calculation. It accepts the same data types as the startdate argument, listed above. The DATEDIFF function calculates the difference between startdate and enddate, with the result representing enddate minus startdate.

Return Types

The DATEDIFF function returns a value of type int.

Return Value

The function returns an integer value representing the count of datepart boundaries crossed between the startdate and enddate. In simpler terms, it’s the difference between the two dates, expressed in the units specified by datepart.

For instance, the SQL statement SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); returns -2. This result indicates that if we start at 2036-03-01 and move back 2 ‘day’ boundaries, we arrive at 2036-02-28. The negative value signifies that the startdate is later than the enddate when considering the specified datepart. The example also subtly highlights that 2036 is a leap year due to February having 29 days.

It’s crucial to be aware of the potential for overflow. If the calculated difference falls outside the valid range for the int data type (-2,147,483,648 to +2,147,483,647), DATEDIFF will return an error. For example, when using millisecond as the datepart, the maximum permissible difference between startdate and enddate is approximately 24 days, 20 hours, 31 minutes, and 23.647 seconds. For second, the maximum difference extends to around 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds.

If both startdate and enddate only contain time values, and the chosen datepart is not a time-related datepart (like hour, minute, second, etc.), DATEDIFF will return 0. This is because there are no date boundaries crossed when only time values are considered with a date-related datepart.

DATEDIFF is sensitive to the time zone offset component if startdate or enddate are of data types that include time zone offset information (like datetimeoffset). The calculation will take these time zone offsets into account when determining the difference.

Because the smalldatetime data type has a precision of only one minute, any seconds and milliseconds components are effectively treated as 0 when either startdate or enddate is of the smalldatetime type. This means the returned difference will be rounded to the nearest minute.

When only a time value is assigned to a date data type variable (like date, datetime, datetime2), DATEDIFF will use the default date portion: 1900-01-01. Conversely, if only a date value is assigned to a variable of a time or date data type that can also hold time (like time, datetime, datetime2), the missing time part defaults to 00:00:00. In scenarios where one date argument has only a time part and the other only a date part, DATEDIFF applies these default values to the missing parts accordingly.

If startdate and enddate are of different date data types, and one has more time parts or fractional seconds precision than the other, DATEDIFF will implicitly set the missing parts of the less precise data type to 0 for the purpose of the calculation. This ensures consistent comparison and difference calculation.

datepart Boundaries

The crucial aspect of DATEDIFF lies in how it counts boundaries. It counts the number of times the boundary of the specified datepart is crossed between the startdate and enddate. Consider the following examples where the startdate and enddate are extremely close in time, differing by just 100 nanoseconds (0.0000001 second).

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(weekday, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1
SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); -- Returns 1

In each case, despite the dates being almost identical, the function returns 1. This is because the small time difference crosses a boundary for each of the specified dateparts (year, quarter, month, etc.). The function is counting the transition from one period to the next.

If startdate and enddate fall within different years but belong to the same calendar week, DATEDIFF with datepart as week will return 0. This is because the calendar week boundary hasn’t been crossed, even though the year boundary has. The week is considered the same despite spanning across two different years.

Remarks

The DATEDIFF function is versatile and can be used in various clauses of your SQL queries, including SELECT, WHERE, HAVING, GROUP BY, and ORDER BY. This allows you to incorporate date difference calculations directly into your data retrieval and manipulation processes.

DATEDIFF implicitly converts string literals to the datetime2 data type. This means that when you provide dates as strings, DATEDIFF interprets them as datetime2. However, DATEDIFF does not support the YDM format (Year-Day-Month) directly when the date is passed as a string. If you need to use the YDM format, you must explicitly cast the string to either a datetime or smalldatetime data type before using it in DATEDIFF.

The SET DATEFIRST setting, which configures the first day of the week, has no impact on DATEDIFF. DATEDIFF consistently uses Sunday as the first day of the week for all calculations involving week or weekday as the datepart. This ensures predictable and deterministic behavior of the function, regardless of the DATEFIRST setting.

It’s important to be mindful of potential overflow issues with DATEDIFF, particularly when using dateparts with high precision like minute or finer. If the difference between enddate and startdate is excessively large, resulting in a value outside the range of the int data type, an overflow error will occur. In such cases, consider using DATEDIFF_BIG which supports BIGINT return type to accommodate larger differences.

Examples

The following examples demonstrate various ways to use the DATEDIFF function with different types of inputs for the startdate and enddate arguments. These examples are based on the AdventureWorks2022 or AdventureWorksDW2022 sample databases, which are available for download from Microsoft SQL Server Samples and Community Projects.

A. Specifying Columns for startdate and enddate

This example calculates the difference in days between dates stored in two columns, startDate and endDate, within a table named Duration.

CREATE TABLE dbo.Duration ( startDate DATETIME2, endDate DATETIME2 );
INSERT INTO dbo.Duration (startDate, endDate) VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09');
SELECT DATEDIFF(day, startDate, endDate) AS [Duration] FROM dbo.Duration;

Here’s the result set:

Duration
--------
1

This query calculates the number of day boundaries crossed between the startDate and endDate for each row in the Duration table.

B. Specifying User-Defined Variables for startdate and enddate

In this example, user-defined variables @startdate and @enddate are used as arguments for DATEDIFF.

DECLARE @startdate AS DATETIME2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate AS DATETIME2 = '2007-05-04 12:10:09.3312722';
SELECT DATEDIFF(day, @startdate, @enddate);

This code snippet calculates the difference in days between the two datetime values stored in the variables.

C. Specifying Scalar System Functions for startdate and enddate

This example demonstrates using scalar system functions GETDATE() and SYSDATETIME() as inputs to DATEDIFF to find the difference in milliseconds between the current date and time obtained from these functions.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());

This is useful for measuring very short durations or performance differences within SQL Server.

D. Specifying Scalar Subqueries and Scalar Functions for startdate and enddate

This example showcases the use of scalar subqueries and scalar functions within DATEDIFF. It calculates the difference in days between the earliest OrderDate and the latest OrderDate found in the SalesOrderHeader table of the AdventureWorks2022 database.

USE AdventureWorks2022;
GO
SELECT DATEDIFF(day, (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader), (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));

This query provides insight into the span of order dates within the sales order data.

E. Specifying Constants for startdate and enddate

This example uses character string constants directly as startdate and enddate to calculate the difference in days between two specific dates.

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635');

This is a straightforward way to calculate date differences when the dates are known beforehand.

F. Specifying Numeric Expressions and Scalar System Functions for enddate

This example demonstrates the flexibility of DATEDIFF by using a numeric expression (GETDATE() + 1) and scalar system functions (GETDATE and SYSDATETIME) to define the enddate. It calculates the number of days between a fixed date (‘2007-05-07…’) and dates derived from system functions, effectively showing the number of days from a specific date to “tomorrow”.

USE AdventureWorks2022;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
USE AdventureWorks2022;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', DATEADD(day, 1, SYSDATETIME())) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO

These queries illustrate how DATEDIFF can be combined with other functions and expressions for dynamic date calculations.

G. Specifying Ranking Functions for startdate

This example uses the ROW_NUMBER() ranking function as the startdate argument. While it might not be a typical use case for date difference calculation, it demonstrates the capability of using ranking function results within DATEDIFF. It calculates the difference in days between a row number (ordered by postal code) and the current system date and time.

USE AdventureWorks2022;
GO
SELECT p.FirstName, p.LastName,
       DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY a.PostalCode), SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
    ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
    ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

This example is more for illustrative purposes, showing the syntax flexibility of DATEDIFF.

H. Specifying an Aggregate Window Function for startdate

This example utilizes an aggregate window function MIN(soh.OrderDate) OVER (PARTITION BY soh.SalesOrderID) as the startdate. It calculates the difference in days between the minimum order date for each SalesOrderID and the current system date and time. This effectively shows how long ago the first order was placed for each sales order.

USE AdventureWorks2022;
GO
SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty, soh.OrderDate,
       DATEDIFF(day, MIN(soh.OrderDate) OVER (PARTITION BY soh.SalesOrderID), SYSDATETIME()) AS 'Total'
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN (43659, 58918);
GO

This demonstrates using window functions to derive dynamic startdate values for DATEDIFF calculations within a result set.

I. Finding Difference Between startdate and enddate as Date Parts Strings

This example shows how to break down the date difference into years, months, days, hours, minutes, seconds, and milliseconds and present it as a formatted string. Note that this example does not account for leap years for month and day calculations beyond years.

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 AS DATETIME, @date2 AS DATETIME, @result AS VARCHAR (100);
DECLARE @years AS INT, @months AS INT, @days AS INT, @hours AS INT, @minutes AS INT, @seconds AS INT, @milliseconds AS INT;

SET @date1 = '1900-01-01 00:00:00.000';
SET @date2 = '2018-12-12 07:08:01.123';

SELECT @years = DATEDIFF(yy, @date1, @date2);
SELECT @months = DATEDIFF(mm, DATEADD(yy, @years, @date1), @date2);
SELECT @days = DATEDIFF(dd, DATEADD(mm, @months, DATEADD(yy, @years, @date1)), @date2);
SELECT @hours = DATEDIFF(hh, DATEADD(dd, @days, DATEADD(mm, @months, DATEADD(yy, @years, @date1))), @date2);
SELECT @minutes = DATEDIFF(mi, DATEADD(hh, @hours, DATEADD(dd, @days, DATEADD(mm, @months, DATEADD(yy, @years, @date1)))), @date2);
SELECT @seconds = DATEDIFF(ss, DATEADD(mi, @minutes, DATEADD(hh, @hours, DATEADD(dd, @days, DATEADD(mm, @months, DATEADD(yy, @years, @date1))))), @date2);
SELECT @milliseconds = DATEDIFF(ms, DATEADD(ss, @seconds, DATEADD(mi, @minutes, DATEADD(hh, @hours, DATEADD(dd, @days, DATEADD(mm, @months, DATEADD(yy, @years, @date1))))))), @date2);

SET @result = CAST (@years AS VARCHAR (10)) + ' years, ' + CAST (@months AS VARCHAR (10)) + ' months, ' +
               CAST (@days AS VARCHAR (10)) + ' days, ' + CAST (@hours AS VARCHAR (10)) + ' hours, ' +
               CAST (@minutes AS VARCHAR (10)) + ' minutes' + ' and ' + CAST(@seconds AS VARCHAR(10))
               + CASE WHEN @milliseconds > 0 THEN '.' + CAST (@milliseconds AS VARCHAR (10)) ELSE '' END + ' seconds';
SELECT @result;

Here’s the result set:

118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds

This example provides a more human-readable breakdown of the total date difference.

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

These examples are tailored for Azure Synapse Analytics and Analytics Platform System (PDW) environments and illustrate similar concepts as the previous examples, but using a different sample dataset (AdventureWorks).

J. Specifying Columns for startdate and enddate (Synapse Analytics/PDW)

This example mirrors Example A but is adapted for Synapse Analytics or PDW, calculating the day difference from columns in a Duration table.

CREATE TABLE dbo.Duration ( startDate DATETIME2, endDate DATETIME2 );
INSERT INTO dbo.Duration (startDate, endDate) VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09');
SELECT TOP (1) DATEDIFF(day, startDate, endDate) AS [Duration] FROM dbo.Duration;

Duration
--------
1

K. Specifying Scalar Subqueries and Scalar Functions for startdate and enddate (Synapse Analytics/PDW)

This example is analogous to Example D, using scalar subqueries to find the difference in days between the minimum and maximum HireDate from the DimEmployee table in the AdventureWorks dataset within Synapse Analytics or PDW.

-- Uses AdventureWorks
SELECT TOP (1) DATEDIFF(day, (SELECT MIN(HireDate) FROM dbo.DimEmployee), (SELECT MAX(HireDate) FROM dbo.DimEmployee)) FROM dbo.DimEmployee;

L. Specifying Constants for startdate and enddate (Synapse Analytics/PDW)

Similar to Example E, this example uses constant date strings to calculate the day difference in a Synapse Analytics/PDW context.

-- Uses AdventureWorks
SELECT TOP (1) DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635') FROM DimCustomer;

M. Specifying Ranking Functions for startdate (Synapse Analytics/PDW)

This example, mirroring Example G, demonstrates the use of ROW_NUMBER() in Synapse Analytics/PDW, calculating the day difference between a row number (ordered by DepartmentName) and the current system date.

-- Uses AdventureWorks
SELECT FirstName, LastName, DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY DepartmentName), SYSDATETIME()) AS RowNumber
FROM dbo.DimEmployee;

N. Specifying an Aggregate Window Function for startdate (Synapse Analytics/PDW)

Analogous to Example H, this example uses MAX(HireDate) OVER (PARTITION BY DepartmentName) in Synapse Analytics/PDW to calculate the year difference between the latest hire date within each department and the current system date.

-- Uses AdventureWorks
SELECT FirstName, LastName, DepartmentName, DATEDIFF(year, MAX(HireDate) OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue
FROM dbo.DimEmployee;

Related Content

• DATEDIFF_BIG (Transact-SQL)
• Date and Time Data Types and Functions (Transact-SQL)