CODEWITHSUNDEEP
sqlsql-serverdatetimerange
mmm
mmm

Reputation: 67

Time range- Sql

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

Upvotes: 3

Views: 3110

Answers (3)

Allan W
Allan W

Reputation: 590

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))

Upvotes: 3

Hogan
Hogan

Reputation: 70513

I guess @gbn is not going to help you. I will try and fill in.

Given -- a table called timedata that has ranges only going over at most one day

WITH normalized AS
(
   SELECT * 
   FROM timedata
   WHERE datepart(day,start_time) = datepart(day,endtime)
   UNION ALL
   SELECT id, rate, start_time, dateadd(second,dateadd(day,datediff(day,0,end_time),0),-1) as end_time 
   FROM timedata
   WHERE not (datepart(day,start_time) = datepart(day,endtime))
   UNION ALL
   SELECT id, rate,dateadd(day,datediff(day,0,end_time),0) as start_time, end_time
   FROM timedata
   WHERE not (datepart(day,start_time) = datepart(day,endtime))
)
SELECT * 
FROM normalized
WHERE datepart(hour,start_time) < @inhour
  AND datepart(hour,end_time) > @inhour

This makes use of a CTE and a trick to truncate datetime values. To understand this trick read this question and answer: Floor a date in SQL server

Here is an outline of what this query does:

Create a normalized table with each time span only going over one day by

  • Selecting all rows that occur on the same day.

Then for each entry that spans two days joining in

  • Selecting the starttime and one second before the next day as the end time for all that span.

and

  • Selecting 12am of the end_time date as the starttime and the end_time.

Finally you perform the select using the hour indicator on this normalized table.

If your ranges go over more than one day you would need to use a recursive CTE to get the same normalized table.

Upvotes: 0

Bryce Siedschlaw
Bryce Siedschlaw

Reputation: 4226

I don't really ever use MS SQL, but maybe this will help.

I was going to suggest something like this, but by the way you have your data set up, this would fail.

SELECT id FROM RATES WHERE datepart(hh, start_time) <= 9 AND datepart(hh, end_time) >= 9;

You'll have you search using the actual date if you expect to get the correct data back.

SELECT id FROM RATES WHERE start_time <= '2011-1-1 9:00' AND end_time >= '2011-1-1 9:00';

This may not be exactly correct, but it may help you look in the right direction.

Upvotes: 0

Related Questions