How to calculate the difference between two dates with T-SQL?

Calculate the difference between two dates in hours, minutes, and seconds with SQL Server functions.

With the SQL Server time function DATEDIFF, it is possible to calculate the difference between two dates in hours, minutes, and seconds but only in one unit at a time. For example, only seconds, or only minutes or only hours. This SQL Server script uses the DATEDIFF function and calculate in hours, minutes, and seconds. So, calculating the difference between a start date and end date is not straightforward with SQL Server. This post shows you how to do so with a simple example query, explained step by step.

1. Time difference in hours minutes and seconds with SQL Server

Therefore, as an example, these simple SQL queries are using DATEDIFF or DATEPART with two timestamps that 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. Here is an example of how to do it.

The queries below show 3 different types of results. However, every time unit is not a time but only one single unit. So, it is mandatory to concatenate them and convert them.

1. First, calculate the difference in seconds
2. Then in minutes
3. 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 minutes:

-- 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.

2. Difference between two timestamps in one T-SQL query

For instance, to get the difference in hours, minutes, and seconds, use this query, it works only if hours are smaller than 99. Moreover, change the total in seconds in Hours, Minutes and Seconds, for example with 3 hours = 3600 * 3 = 14400.

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 difference using the DATEADD function

Indeed, these SQL 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). Certainly, it finally displays the result without the milliseconds using the CONVERT function and the data date format 108.

-- 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. Calculate the difference between two dates using the DATEPART function

Another option to calculate the difference in minutes and seconds is to use the T-SQL DATEPART function.

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];

Conclusion on time difference calculation in T-SQL

In this brief article, we will discuss how to calculate the difference between two SQL Server dates with T-SQL. By utilizing the DATEDIFF function, you can easily determine the difference in hours, minutes, and seconds between two dates. To optimize your solution, we suggest combining multiple functions. For more information on the SQL Server DATEADD() time function, you can refer to the official Microsoft documentation.

Questions about SQL Server time functions

To go further with T-SQL useful queries and tips

• Query to display the month name in SQL Server
• Manage text variables with more than 8000 characters in T-SQL
• 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