Time range- Sql

Question

please help me with my problem. So, I have a table named 'RATES' which contains these columns:

id (int) 
rate (money) 
start_time (datetime) 
end_time(datetime)

example data:

1 150 8:00am 6:00pm 
2 200 6:00pm 4:00am
3 250 8:00am 4:00am (the next day)

What I have to do is to select all the id(s) to where a given time would fall.

e.g given time: 9:00 pm, the output should be 2,3

The problem is I got this time range between 8am to 4am the next day and I don't know what to do. Help, please! thanks in advance :D

Answer 1

Assuming that @Andriy M is correct:

• Data never spans more than 24 hours
• if end_time<=start_time then end_time belongs to the next day

then what you're looking for is this:

Declare @GivenTime DateTime
Set @GivenTime = '9:00 PM'
Select ID
  From Rates
 Where (Start_Time<End_Time And Start_Time<=@GivenTime And End_Time>=@GivenTime)
    Or (Start_Time=End_Time And Start_Time=@GivenTime)
    Or (Start_Time>End_Time And (Start_Time>=@GivenTime Or End_Time<=@GivenTime))