CODEWITHSUNDEEP
sql-servert-sql
Kumar_2002
Kumar_2002

Reputation: 604

T-sql time logic

I have 3 columns data like 

storeID  starttime  endtime
   A       1:00AM    4:00PM
   B       7:30PM    6:00AM
   C       2:00AM    4:00AM

I have to check the given time falls between the two time intervals.

Can you suggest a T-SQL statement for that logic?

If suppose if I want to get stores which are open at 1:00AM, I have to get A, B as the result.

Upvotes: 1

Views: 127

Answers (2)

SQL Dabbler
SQL Dabbler

Reputation: 96

declare @testTime time 
select @testTime = '01:00' 
select storeId from openinghours where @testime between starttime and endtime

Upvotes: 0

AdamH
AdamH

Reputation: 1351

declare @testTime time
select @testTime = '01:00'
select storeId from openinghours 
    where (startTime<=endTime and @testTime >=startTime and @testTime <=endTime) 
        OR (startTime>endTime and (@testTime <startTime OR @testTime > endTime))

Upvotes: 1

Related Questions