Click here to Skip to main content
15,747,637 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I need to get data from table in SQL DB for particular time duration. table as below

ID Start Time End Time
1 07:00 15:00
2 15:00 23:00
3 23:00 07:00

I need to design query which returns ID on current system time

What I have tried:

select id from TableName where convert(time, start time) <= convert(time, GETDATE()) and convert(time, end time) >= convert(time, GETDATE())

this works fine for ID 1 and 2 but not for ID 3
Updated 13-Jun-23 22:25pm
Richard MacCutchan 9-Jun-23 5:41am    
Store them as SateTime values and the problem should be solved.

Firstly, those Convert statements make me suspect you're storing your times as varchar, which is absolutely the wrong thing to do. Aside from the fact that there is no validation to ensure the column value is a valid time, it will also take up a lot more space than using the correct column type, and requires you to add those Convert statements to every query. You should look to change the type of those columns to time(0) instead.

Date and Time Data Types and Functions - SQL Server (Transact-SQL) | Microsoft Learn[^]

Secondly, the cause of your issue is clear: you are looking for records which start before the current time and end after the current time. But record 3 ends before it starts, so it can never match that condition.

One option would be to split record 3 into two records: one from 23:00 to 23:59, and one from 00:00 to 07:00.

Otherwise, you need to change your condition to account for records spanning midnight:
DECLARE @now time(0) = SYSDATETIME();

FROM TableName
    -- Normal time range:
    (StartTime <= EndTime And StartTime <= @now And @now <= EndTime)
    -- Time range spans midnight:
    (StartTime > EndTime And (StartTime <= @now Or @now <= EndTime))
Share this answer
not helpful. not getting data on same
Share this answer
Richard Deeming 14-Jun-23 4:30am    
Not a solution.

And simply stating "not helpful" without any further information is itself extremely not helpful.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900