How to compare two dates to find time difference in SQL Server 2005, date manipulation

sql

date-manipulation

sql-server

datetime

byAnton Shumikhin·Feb 15, 2025

In SQL Server 2005, use the DATEDIFF function to calculate the time difference between two datetime values. Indicate your desired time unit—seconds, minutes, or hours—then specify the two dates. Here's an example for hours:

SELECT DATEDIFF(hour, '2023-01-01 10:00', '2023-01-02 10:00') AS HoursDifference;

Query result is 24: that's hours diff. No time machine required!

Achieve more precise results—down to milliseconds—with DATEDIFF:

SELECT DATEDIFF(ms, '2023-01-01 10:00:00.000', '2023-01-01 10:00:01.123') AS MillisecondsDifference;

Voila! You get 1123 milliseconds. That's precision at the pace of a lightning bolt!

Fractions of time: splitting seconds

Dealing with fractions of seconds? Time intervals? We got you covered. Feed DATEDIFF to CAST or CONVERT along with DATEADD:

SELECT CAST(DATEADD(ms, DATEDIFF(ms, start_time, end_time), 0) AS TIME) AS TimeInterval
FROM your_table;

A great illusionist's trick: make the time difference appear as a time format itself!

Time precision: cutting out milliseconds

Much ado about nothing! Drop milliseconds from your time result for a cleaner look with AS TIME(0):

SELECT CAST(DATEADD(ms, DATEDIFF(ms, start_time, end_time), 0) AS TIME(0)) AS PreciseWithoutFractions
FROM your_table;

Say goodbye to those pesky fractions of a second!

User-friendly time results

Pretty printing is easy with CONVERT. Format your time difference into an attractive HH:MM:SS format:

SELECT CONVERT(VARCHAR, CAST(DATEADD(ms, DATEDIFF(ms, start_time, end_time), 0) AS TIME), 120) AS FormattedTime
FROM your_table;

Your time differences now render in party dresses instead of scruffy coder tees!

Getting the time difference to today

Check the time difference between a stored date and right now using GETDATE():

SELECT DATEDIFF(day, your_datetime_column, GETDATE()) AS DaysToNow
FROM your_table;

Yes, SQL Server is that sentient. It's your personal timekeeper!

Prioritizing precision with the correct datatype

Always use the DATETIME datatype for your dates & times—don't settle for less!

DECLARE @Start DATETIME = '2023-01-01 14:30';
DECLARE @End DATETIME = '2023-01-02 15:45';
SELECT DATEDIFF(mi, @Start, @End) AS MinutesDifference;

Arduino precision in SQL Server! Savor those microseconds. Your application users will thank you!

The power of string manipulation

Works like magic! Run SUBSTRING and CONVERT on DATETIME to slice and dice your date:

SELECT 
SUBSTRING(CONVERT(VARCHAR, your_datetime_column, 120), 12, 5) AS TimeHHMM
FROM your_table;

Just like a time traveling barber: give your datetime a new style!

Accurate calculations: setting the stage

Got a complex script? SET your date and time at the start to avoid unexpected surprises:

SET @StartTime = (SELECT start_time FROM your_table WHERE ID = 1);
SET @EndTime = (SELECT end_time FROM your_table WHERE ID = 1);
SELECT DATEDIFF(minute, @StartTime, @EndTime) AS WorkDuration;

This SET isn’t for your stage play—it's for your SQL blockbuster!

Storing dates for easier manipulation

No more headaches—store your dates in a table for a smoother querying experience:

CREATE TABLE TimeRecords (
    ID INT PRIMARY KEY,
    StartTime DATETIME NOT NULL,
    EndTime DATETIME NOT NULL
);

-- Querying becomes intuitive
SELECT StartTime, EndTime, 
DATEDIFF(ss, StartTime, EndTime) AS SecondsDifference
FROM TimeRecords;