SQL DATEDIFF Function

The SQL Server DATEDIFF function calculates the date or time difference between the Start and End Dates. The DATEDIFF function always returns a signed integer value, which can be either positive or negative. If the start date is less than the end date, it returns a positive number; otherwise, it returns a negative number. For example, if you want to find the difference between two dates in terms of the year, month, quarter, hours, and seconds, you can use the DATEDIFF function.

The SQL DATEDIFF function uses the DATEPART value to show the total number of boundaries crossed between the given two dates in terms of years, days, weeks, and so on. So, it provides data analysts with the ability to easily measure date and time intervals for the analysis and reporting. Some of the useful scenarios of the DATEDIFF function are:

Calculating the Age using the birth date and today’s date.
The number of years an Employee has been associated with the company.
Total number of hours between the order and shipping date.
Time taken to resolve a ticket.
Duration or total time period of an event.
Time in terms of hours or minutes between each transaction.
Calculating the operational time.
Filtering the data by a certain duration using a WHERE clause and the SQL Server DATEDIFF function.

To explain simply, let me provide a basic example to calculate the total number of days’ difference between the order and shipping date.

SELECT DATEDIFF(day, ShippingDate, OrderDate) AS difference;

SQL Server DATEDIFF Function Syntax

The syntax of the DATEDIFF function to find the difference between two dates and returns an integer value as the output is shown below.

DATEDIFF(Datepart, Start_Date, End_Date)

As you can see from the above syntax, the SQL DATEDIFF function accepts three arguments, and they are

Datepart: This is part of a given date that specifies the units of difference the function has to calculate between the Start and End Date. For instance, year, day, etc.
Start_date: Please specify the valid date as the second argument (Starting Date). It can be a column, expression, or any SQL Server variable.
End_date: Please specify the valid date as the DATEDIFF third argument (Ending Date). It can be a column, an expression, or any variable.

TIP: Both the start and end dates accept the date, datetime, datetime2, datetimeoffset, smalldatetime, and time data types as the argument values.

The list of available datepart arguments in SQL Server for this DATEDIFF.

Datepart	Abbreviations	Description
year	yy,yyyy	Display the difference between the Start and End date in terms of Years
quarter	qq, q	It measures the difference between the Start and End dates in terms of Quarters.
month	mm, m	Display the difference between the Start and End date in terms of Months.
dayofyear	dy, y	Display the difference between the Start and End date in terms of Days of a Year (1 to 365).
day	dd, d	SQL Server DATEDIFF displays the difference between the Start and End dates in terms of Days (1 to 31).
week	wk, ww	Display the difference between Start and End in terms of the Week.
weekday	dw, w	Display the difference between Start and End in terms of Weekday
hour	hh	This Datepart in it displays the difference between Start and End in terms of Hours
minute	mi, n	Display the difference between the Start and End in terms of minutes
second	ss, s	It displays the difference between Start and End in seconds.
millisecond	ms	Display the difference between the Start and end date in terms of Milliseconds.
microsecond	mcs	This DATEDIFF argument displays the difference between the Start and End dates in terms of microseconds.
nanosecond	ns	It will print the difference between Start and End in terms of Nanoseconds.

NOTE: You can’t use a variable as the DATEPART value. Also, don’t enclose the value within the single quote, such as ‘year’.

SQL DATEDIFF Function to find DATE and Time Difference

In general, you can use the DATEDIFF function in a SELECT statement, but you are limited to it, so apply it on WHERE, GROUP BY, HAVING, and ORDER BY clauses.

In this DATEDIFF example, we will declare two variables of the datetime2 data type. Let us assign a valid date to both variables and perform all the available operations.

First, we declared two variables, @ab and @cd, and assigned different dates and times to those variables. Within the first statement (year, @ab, @cd), the SQL DATEDIFF function prints the total number of years between the @ab variable and @cd.

Next, we asked the DATEDIFF function to display the total number of months between the @ab and @cd variables.

DECLARE @ab datetime2 = '2015-01-01 09:12:54.1234567',
		@cd datetime2 = '2015-08-25 14:24:04.1234567'

SELECT 'YEAR' AS [Names], DATEDIFF(year, @ab, @cd) AS [Values] 
UNION ALL
SELECT 'QUARTER', DATEDIFF(quarter, @ab, @cd) 
UNION ALL
SELECT 'MONTH', DATEDIFF(month, @ab, @cd) 
UNION ALL
SELECT 'DAYOFYEAR', DATEDIFF(dayofyear, @ab, @cd) 
UNION ALL
SELECT 'DAY', DATEDIFF(day, @ab, @cd) 
UNION ALL
SELECT 'WEEK', DATEDIFF(week, @ab, @cd) 
UNION ALL
SELECT 'WEEKDAY',DATEDIFF(weekday, @ab, @cd) 
UNION ALL
SELECT 'HOUR', DATEDIFF(hour, @ab, @cd) 
UNION ALL
SELECT 'MINUTE', DATEDIFF(minute, @ab, @cd) 
UNION ALL
SELECT 'SECOND', DATEDIFF(second, @ab, @cd)

The result set returned by the above SQL Server DATEDIFF function query is:

Names	Values
YEAR	0
QUARTER	2
MONTH	7
DAYOFYEAR	236
DAY	236
WEEK	34
WEEKDAY	236
HOUR	5669
MINUTE	340152
SECOND	20409070

Using Scalar System Functions

The query below shows how to use the system scalar functions, such as GETDATE() and SYSDATETIME(), and finds the difference between them in milliseconds and nanoseconds.

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

SELECT DATEDIFF(nanosecond, GETDATE(), SYSDATETIME()) ns;

ms
-----------
3

ns
-----------
3183000

Using the Aggregate function

The following example query uses the MIN and MAX functions in the SQL DATEDIFF function to calculate the total number of months and weeks difference between the minimum and maximum order date.

SELECT DATEDIFF(month, 
    (SELECT MIN(OrderDate) FROM FactInternetSales), 
    (SELECT MAX(OrderDate) FROM FactInternetSales)) AS Months,
    DATEDIFF(week, 
    (SELECT MIN(OrderDate) FROM FactInternetSales), 
    (SELECT MAX(OrderDate) FROM FactInternetSales)) AS Weeks

Months      Weeks
----------- -----------
37          161

SQL DATEDIFF Function on Table Columns

In this example, we use the custom table to perform DATEDIFF operations on the Hire column and the built-in GETDATE() function. The first statement in the 5th line finds the difference between the Hire Column and today in terms of years.

In the next line, this DATEDIFF Function will find the difference between the Hire Column and today regarding Quarters.

By adding the day as the first argument, it calculates the number of days passed between the Hire date and the current date. Similarly, you can select milliseconds, hours, minutes, the number of days, and days hours.

SELECT  [FirstName] + ' ' + [LastName] AS Name
      ,[Occupation],[Sales],[HireDate]
      ,DATEDIFF(yyyy, HireDate, GETDATE()) AS Year
      ,DATEDIFF(q, HireDate, GETDATE()) AS Quarter
      ,DATEDIFF(m, HireDate, GETDATE()) AS Month
      ,DATEDIFF(d, HireDate, GETDATE()) AS Day     
      ,DATEDIFF(week, HireDate, GETDATE()) AS Week
      ,DATEDIFF(hour, HireDate, GETDATE()) AS Hour
  FROM [Employee]

Using CASE Statement

If you use the SQL DATEDIFF function in a CASE statement, you can write custom messages or perform custom operations based on the difference between two dates. For instance, the query below provides a message to the team about the bonus. If the association is above 15, it gets a double bonus, above 12 years’ bonus, and the rest of the employees get no bonus.

SELECT [EmpID]
      ,[FirstName] + ' ' + [LastName] AS Name
      ,[Occupation],[Sales],[HireDate]
      ,CASE WHEN DATEDIFF(yyyy, HireDate, GETDATE()) > 15 THEN 'Double Bonus'
      WHEN DATEDIFF(yyyy, HireDate, GETDATE()) > 12 THEN 'Bonus'
      ELSE 'No Bonus' 
      END AS Message
  FROM [Employee]

SQL DATEDIFF Function in WHERE Clause

Apart from using the DATEDIFF function in the SELECT statement to calculate the difference, you can use it in the WHERE clause to filter the date based on the difference. For instance, showing all the employees whose login is above 10 AM or finding the customer who hasn’t logged in for seven or 10 days.

The query below displays all employees who have been associated with the company for more than 15 years.

SELECT  [EmpID]
      ,[FirstName] + ' ' + [LastName] AS Name
      ,[Occupation],[Sales],[HireDate]
      ,DATEDIFF(yyyy, HireDate, GETDATE()) AS Year
  FROM [Employee]
  WHERE DATEDIFF(yyyy, HireDate, GETDATE()) > 15

Common DATEDIFF Errors: Fixing them!

While working with the SQL DATEDIFF function, due to multiple arguments and a set of instructions, users easily make mistakes. Here, we will list the most common errors and fix them.

Returning Negative Value

It is a general practice to use the highest date as the start_date and the lowest one as the third argument. If it is the case, the DATEDIFF function returns a positive integer; otherwise, it returns a negative number. For instance, the query below uses a small value as the second argument compared to the third one.

-- start < end date
SELECT DATEDIFF(day, '2025-09-18', '2025-09-11') AS r1; 
--start time < end
SELECT DATEDIFF(hh, '2025-09-18 23:07:22', '2025-09-18 10:22:44') AS r2;

r1
-----------
-7

r2
-----------
-13

TIP: Although negative values are interpreted as wrong values, checking the actual delivery vs deadlines indicates how effective the order dispatch is.

Inconsistent Date Formats

When storing dates in a column, based on the local system configuration, the date values will be stored. For instance, one system may store MM/DD/YYYY and another may store DD/MM/YYYY. When you use these inconsistent values, you might get the wrong result if the date is less than 12; otherwise, the SQL DATEDIFF function throws an error.

The query below throws an error saying it can’t convert.

SELECT DATEDIFF(day, '2025/09/18','2025/13/11') AS r3;

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

However, this one returns the result because it does not matter whether the separation is / or -, the DATEDIFF function implicitly converts it to a datetime object.

SELECT DATEDIFF(day, '2025/09/18','2025-10-11') AS r4;

r4
-----------
23

Overflow or Out of Range

When using the minute, second, millisecond, microsecond, or nanosecond, you must be careful with the start and end date values. As we mentioned before, the SQL DATEDIFF function returns an INTEGER data type (-2,147,483,648 to +2,147,483,647), and any value that exceeds that number is considered an overflow or out-of-range error. For instance, the query below throws an overflow error.

SELECT DATEDIFF(ms, '2025-09-18 23:07:22','2025-05-18 10:22:44') AS r5;

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.

To solve the above overflow error, use the SQL DATEDIFF_BIG() function.

SELECT DATEDIFF_BIG(ms, '2025-09-18 23:07:22','2025-05-18 10:22:44') AS r6;

r6
--------------------
-10673078000

Using a Single Quote in the first argument

As we mentioned earlier, you should not use the single quote for the DATEPART unit to calculate the difference.

SELECT DATEDIFF('month', '2025-09-18', '2025-05-18') AS r7

Msg 1023, Level 15, State 1, Line 1
Invalid parameter 1 specified.

To resolve the above error, use

SELECT DATEDIFF(month, '2025-09-18', '2025-05-18') AS r8

r8
-----------
-4

Zero Output

When working with the SQL DATEDIFF function, sometimes you will encounter zero as the difference between two dates. For instance, if you provide the same day with different times as the second and third arguments and use day, month, or year as the first argument, it returns zero.

In the query below, both statements return zero because 10′, there is an 8-day difference between ‘2025-09-18’ and ‘2025-09-not a month. Similarly, in the second statement, there is a month difference but not a year.

SELECT DATEDIFF(month, '2025-09-18', '2025-09-10') AS r9

SELECT DATEDIFF(year, '2025-09-18', '2025-03-10') AS r10

r9
-----------
0

r10
-----------
0

Missing Date Value

If there is any missing date part in either argument 2 or 3, the SQL DATEDIFF function assigns the default 1900-01-01 value. Here, 23:07:22 was implicitly converted to 1900-01-01 23:07:22.

SELECT DATEDIFF(year, '23:07:22', '2025-09-18 10:22:44') AS r11;

r11
-----------
125

Missing Time Value

If there is any missing time part in either argument 2 or 3, the DATEDIFF function assigns the default 00:00:00 value. Here, 2025-09-18 will convert to 2025-09-18 00:00:00.

SELECT DATEDIFF(hh, '2025-09-18', '2025-09-18 10:22:44') AS r12;

r12
-----------
10

Handling NULL Values

If any of the argument values is NULL, the SQL DATEDIFF function will return the result as NULL. So, handling NULL values is a key factor when implementing this function. For instance, the two statements use NULL as the second and third arguments. It returns the output as NULL.

SELECT DATEDIFF(day, NULL, '2025-05-18') AS n1;

SELECT DATEDIFF(day, '2025-09-10', NULL) AS n2;

n1
-----------
NULL

n2
-----------
NULL

To deal with these NULL values, use either the COALESCE or ISNULL functions to replace NULL with a default date or the current date.

SELECT DATEDIFF(day, '2025-09-10', COALESCE(NULL, GETDATE())) AS n3;

SELECT DATEDIFF(day, '2025-09-10', ISNULL(NULL, GETDATE())) AS n4;

n3
-----------
10

n4
-----------
10