CODEWITHSUNDEEP
sqlsql-server
MaVRoSCy
MaVRoSCy

Reputation: 17839

How to determine if the time of day is between a certain range of hours

I have implemented a calendar type of application where some predefined time ranges exist in my database.

My Predefined Hours Table looks something like this:

Id    int Unchecked
StartTime varchar(50) Unchecked
EndTime   varchar(50) Unchecked

And the values:

Id StartTime EndTime

1 00:00 10:29

2 10:30 12:59

3 13:00 15:59

4 16:00 23:59

How can i determine using sql in which range does my current time falls into

Thanks in advance

Upvotes: 0

Views: 552

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

DECLARE @t char(5)='23:10'
SELECT *
FROM @yourtable
WHERE @t BETWEEN StartTime AND EndTime

Upvotes: 1

Dudi Konfino
Dudi Konfino

Reputation: 1136

DECLARE @t varchar(10)='00:10'
select id 
FROM tbl
where  convert(binary,@t) BETWEEN convert(binary,StartTime) 
       AND 
       convert(binary,EndTime)

Upvotes: 1

deyhle
deyhle

Reputation: 473

You should use a date and time data type instead of varchar, because SQL can understand the value of those types. varchar is just a string and SQL has no idea how to compare those. With a time data type, you can then use BETWEEN for your condition, something like:

(…) CURRENT_TIMESTAMP BETWEEN StartTime AND EndTime (…)

Upvotes: 1

Related Questions