How can I compare time in SQL Server?

Rules of the game: Understanding datetime

Realize that in SQL Server, datetime values are stored as flippin' float. The integer part signifies the date whereas digits saddled beyond the . (dot) represent the time. Consider this, it's somewhat like the 24-hr time format where values before : signify the hour part, values after the : signify the minute part.

Time musketeers: floor, cast and convert

To compare time parts effectively, you can initially convert datetime to float, subsequent to which, floor and cast convert it back to datetime. Here, FLOOR function trims the datetime — splitting date and time victoriously.

SELECT *, CAST(YourDateTimeColumn - CAST(FLOOR(CAST(YourDateTimeColumn AS FLOAT)) AS DATETIME) AS TIME) AS TimeOnly
FROM YourTable


Now, tame 'TimeOnly' in your comparisons.

Say hello to time ranges

For fetching a range of times, employ the trusty BETWEEN clause. Don't forget your routine check with 'HH:MM:SS'. Like BETWEEN '09:00:00' AND '17:00:00'. Records only fall in between 09:00 AM - 05:00 PM as a result.

Format your time knight: Style 108

In order to get a consistent format, use CONVERT function with style 108 to extract time in HH:MM:SS. Run a test to ensure you're not being led astray!

SELECT CONVERT(VARCHAR, YourDateTimeColumn, 108) AS TimePortion


Juggling time variables

Trade complexity for simplicity, isolate, and store time values into variables when you compare multiple time columns.

DECLARE @StartTime TIME = '08:00:00'; -- Early bird start
DECLARE @EndTime TIME = '12:00:00'; -- Everyone loves lunch

SELECT CASE 
         WHEN CAST(EventStart AS TIME) >= @StartTime AND CAST(EventEnd AS TIME) <= @EndTime 
         THEN 'Event within time slot'
         ELSE 'Event outside time slot' -- Night owl or procrastinator event?
       END as Status
FROM Events;


How to handle midnight chimes

Handling edge cases, particularly around midnight, requires extra care. Who said only werewolves come out at night?

-- Time spanning midnight
SELECT *
FROM YourTable
WHERE (CAST(YourDateTimeColumn AS TIME) >= '22:00:00'
       OR CAST(YourDateTimeColumn AS TIME) <= '02:00:00');


Granularity matters!

Your solution should not stutter at fractional seconds, ensure time granularity doesn't leave you in stitches.

Time zones: Not just for geography

Time zones can be your nemesis if neglected. Utilise offsets and the AT TIME ZONE clause to convert times to a identical time zone, ensuring equivalency.