How to Calculate Difference Between Two Dates in T-SQL ?

Learn 4 different methods to get the difference between two dates using SQL Server time functions.

With the SQL Server time functions, it is possible to calculate the difference between two dates in hours, minutes, and seconds. But this calculation is done only in one unit at a time. For example, only seconds, or only minutes or only hours. So its not very useful for one time calculations, because you need to combine them to get the full difference. This SQL Server scripts uses the DATEDIFF, CAST, Modulo or DATEADD functions and calculate difference in hours, minutes, and seconds in one or multiple queries.

1. Get the time difference between two timestamps in T-SQL

First, as an example, these 3 simple SQL Server queries uses the DATEDIFF functions. And the two timestamps stored in variables have exactly one hour of difference. However, each query only returns one unit, which is not enough for our purpose. To get the complete result in hours, minutes, and seconds, we need to concatenate multiple queries together. Indeed, the queries below show 3 different specific time unit as results.

1. First, calculate the difference in seconds
2. Then in minutes
3. The last one in hours

-- Declare and initialize two variables with exactly one hour of difference: 9 - 8 = 1h 
DECLARE @DateStart 	DATETIME
DECLARE @DateEnd 	DATETIME
SET @DateStart 	= '2022-03-10 08:00:00'
SET @DateEnd 	= '2022-03-10 09:00:00'

-- SQL Query returns difference in seconds : 3600 seconds
SELECT DATEDIFF(SECOND, @DateStart, @DateEnd) AS [Difference in Seconds]

-- SQL Query returns difference in minutes : 60 minutes
SELECT DATEDIFF(MINUTE, @DateStart, @DateEnd) AS [Difference in Minutes]

-- SQL Query returns difference in hours : 1 hour
SELECT DATEDIFF(HOUR, @DateStart, @DateEnd) AS [Difference in Hours]

Or in a more concise format, using only 3 lines of code, but calculating only the difference in hours:

-- Declare and initialize two variables with exactly one hour of difference: 9 - 8 = 1h 
DECLARE @DateStart DATETIME = '2022-03-10 08:00:00',
        @DateEnd   DATETIME = '2022-03-10 09:00:00';

-- SQL Query returns difference in hours : 1 hour
SELECT DATEDIFF(HOUR, @DateStart, @DateEnd) AS [Difference in Hours];

Find below a screenshot after the execution of the query using SSMS to query our database.

2. Difference between two dates using SQL Server CAST function

In this second method, to get the time difference in hours, minutes, and seconds, use this query with CAST and the Modulo mathematic operator. However, it works only if the number of hours are smaller than 99. Moreover, the query changes the total in seconds to Hours, Minutes and Seconds, for example with 3 hours = 3600 * 3 = 14400 seconds.

DECLARE @TimeInSeconds INT
SET 	@TimeInSeconds = 14400

SELECT
RIGHT('0' + CAST(@TimeInSeconds / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeInSeconds / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(@TimeInSeconds % 60 AS VARCHAR),2)

3. Calculate the time difference using DATEADD function

In this third approach, these queries computes the time difference using first the seconds (multiplied by one hour in seconds is 60 * 60 = 3600. Then using the milliseconds (multiplied by one hour in milliseconds as 60 * 60 * 1000 = 3600 * 1000). And it finally displays the result without the milliseconds using the CONVERT function and the data date format 108 which is as the following: hh:mi:ss.

-- Using the seconds as a base for conversion
select CONVERT(varchar, dateadd(s, 3 * 3600 , getdate()), 108) AS [Hours Minutes Seconds]

-- Using the milliseconds as a base for conversion
select CONVERT(varchar, dateadd(ms, 3 * 3600 * 1000, getdate()), 108) AS [Hours Minutes Seconds]

4. Get difference between two dates using T-SQL and DATEPART

Another option to calculate the difference in minutes and seconds is to use the T-SQL DATEPART function. This built-in MS SQL function is made on purpose to extract a specific part of the date format.

DECLARE @DateStart DATETIME = '2022-03-10 08:00:00',
        @DateEnd   DATETIME = '2022-03-10 09:00:00';

SELECT 
    DATEPART(HOUR, @DateEnd)   - DATEPART(HOUR, @DateStart)   as [Difference in Hours],
    DATEPART(MINUTE, @DateEnd) - DATEPART(MINUTE, @DateStart) as [Difference in Minutes],
    DATEPART(SECOND, @DateEnd) - DATEPART(SECOND, @DateStart) as [Difference in Seconds];

Four different ways to calculate time differences using SQL Server

In this tutorial, we discussed 4 different ways how to calculate the difference between two SQL Server dates using T-SQL code. By using built-in time functions, you can easily determine the difference in hours, minutes, and seconds. To optimize the solution you have chosen, we suggest combining multiple functions. For more information on the SQL Server DATEADD() time function, you can refer to the official Microsoft documentation.

FAQ about SQL Server time functions

Useful T-SQL queries and tips that might interest you

• Query to display the month name in SQL Server
• Last time table was updated in SQL Server
• Avoid the SQL Server Arithmetic overflow error converting expression
• Check if table exists in SQL Server

SQL Server string longer than 8000 characters