SQL Server Date Difference in Hours

by

In this detailed article, I’ll walk you through various methods for calculating the date difference in hours in SQL Server, providing practical examples and sharing tips on handling fractional hours and time zones. By the end, you’ll be confident handling any date difference calculation in SQL Server.

SQL Server Date Difference in Hours

Why Calculate Date Difference in Hours?

Calculating the difference between two dates in hours is a common requirement, such as:

  • Healthcare: Tracking patient stay durations.
  • Finance: Calculating interest accrual periods.
  • Manufacturing: Monitoring machine uptime or downtime.
  • Retail: Analyzing sales performance over specific hourly intervals.

Understanding the exact hours between two events is crucial for accurate reporting, billing, and operational optimization.

Prerequisites

Before diving into calculations, it’s essential to understand the standard date/time data types in SQL Server:

Data TypeDescriptionTypical Use Case
DATETIMEDate and time with 3.33 ms precisionLegacy systems, general purposes
DATETIME2Date and time with higher precisionNewer applications needing accuracy
SMALLDATETIMEDate and time with minute precisionLess precise time tracking
DATEDate only (no time)Storing dates without time
TIMETime onlyStoring time without date

Most time difference calculations involve DATETIME or DATETIME2.

Method 1: Using DATEDIFF Function

The easiest and most commonly used method to calculate the difference in hours between two dates in SQL Server is the DATEDIFF function.

Syntax

DATEDIFF ( datepart , startdate , enddate )
  • datepart: The part of the date to calculate difference on (e.g., hour, minute).
  • startdate: The beginning date/time.
  • enddate: The ending date/time.

Example: Calculate Whole Hours Difference

SELECT DATEDIFF(hour, '2025-07-20 08:00:00', '2025-07-21 10:30:00') AS HoursDifference;

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server Date Difference in Hours

This counts the number of hour boundaries crossed between the two dates. Note that it returns an integer, so it does not account for fractional hours.

Method 2: Calculating Fractional Hours with DATEDIFF and DateTime Arithmetic

If you want the difference in decimal hours (including fractions), you need to calculate the difference in smaller units (such as seconds or minutes) and then convert it.

Step-by-Step

  1. Calculate the difference in seconds or minutes.
  2. Convert the result to hours by dividing by 3600 (seconds) or 60 (minutes).

Example: Using Seconds for Fractional Hours

SELECT 
    CAST(DATEDIFF(second, '2025-07-20 08:00:00', '2025-07-21 10:30:00') AS FLOAT) / 3600 AS FractionalHours;

After executing the above query, I got the expected output as shown in the screenshot below.

sql server date difference in hours minutes seconds

This approach gives you the exact number of hours, including the fractional part.

Example: Using Minutes for Fractional Hours

SELECT 
    CAST(DATEDIFF(minute, '2025-07-20 08:00:00', '2025-07-21 10:30:00') AS FLOAT) / 60 AS FractionalHours;

After executing the above query, I got the expected output as shown in the screenshot below.

sql date difference in hours

Method 3: Using CAST and CONVERT

Sometimes, you might want to calculate the difference by subtracting datetime values directly and converting the result.

Example: Subtracting and Casting to FLOAT

In SQL Server, subtracting two datetime values returns a result in days (as a float). Multiply by 24 to get hours.

SELECT 
    CAST(CAST('2025-07-21 10:30:00' AS DATETIME2) - CAST('2025-07-20 08:00:00' AS DATETIME2) AS FLOAT) * 24 AS HoursDifference;

Result: 26.5

This method is useful when working with DATETIME2 And when you want to avoid DATEDIFF’s integer limitation.

Check out Operand data type datetime2 is invalid for subtract operator.

Practical Examples

Example 1: Employee Shift Duration

Calculate how many hours an employee worked between clock-in and clock-out.

DECLARE @InTime DATETIME = '2025-07-21 09:15:00';
DECLARE @OutTime DATETIME = '2025-07-21 17:45:00';

SELECT 
    DATEDIFF(minute, @InTime, @OutTime) / 60.0 AS HoursWorked;

After executing the above query, I obtained the expected output, which is 8.5 hours, as shown in the screenshot below.

sql date difference in hours and minutes

Example 2: Machine Uptime Monitoring

Calculate machine uptime in hours from logs.

SELECT 
    MachineID,
    CAST(DATEDIFF(second, StartTime, EndTime) AS FLOAT) / 3600 AS UptimeHours
FROM MachineLogs
WHERE MachineID = 101;

Summary Table of Methods

MethodPrecisionProsCons
DATEDIFF(hour, start, end)Integer hoursSimple and fastNo fractional hours
DATEDIFF(second/minute) / 3600Fractional hoursAccurate fractional calculationsSlightly more complex syntax
Subtracting datetime and multiplying by 24Fractional hoursSimple arithmetic, flexibleRequires casting, careful with types
Using DATETIMEOFFSET and UTC conversionTimezone awareAccurate across time zonesMore complex, requires timezone knowledge

Conclusion

Calculating the difference between two dates in hours is essential for a database developer. You can use the simple DATEDIFF function, along with other methods mentioned in this article, to perform more precise fractional hour calculations.

Remember:

  • Use DATEDIFF for quick integer hour differences.
  • Use seconds or minutes difference divided by 3600 or 60 for fractional hours.
  • Consider time zones and daylight saving when working with US data.

You may also like following the articles below.