Comparing time ranges

Question

I'm trying to write an SQL statement which compares time ranges. I got some excellent ideas from this question:

Comparing date ranges

The problem however is times that span midnight e.g. given the following start and end times:

• 19:00 - 20:00
• 21:00 - 21:30
• 23:00 - 01:00
• 01:00 - 03:00

I want to find any rows where the times intersect/overlap 23:30 - 00:30. The code I have only works if both the rows in the db and the time range I want to check start and end on the same day.

I've tried converting the times to seconds since midnight and then adding 86400 seconds to the end time if the range spans midnight. I've had limited success with that but it still misses some overlapping ranges.

Answer 1

Here is the answer in SQL SERVER - mysql would be similar

DECLARE @StartTime TIME, @EndTime TIME, @CheckTime TIME
SET @StartTime = '20:00'
SET @EndTime = '06:00'
SET @CheckTime = '22:59'

IF @StartTime <= @EndTime
BEGIN
    --same day eg 6pm - 11pm
    IF @CheckTime >= @StartTime AND @CheckTime <= @EndTime
        SELECT 'True'
    ELSE
        SELECT 'False'
END
ELSE
BEGIN
    --overlaps midnight
    IF @CheckTime >= @EndTime OR @CheckTime <= @StartTime 
        SELECT 'true'
    ELSE
        SELECT 'false'
END