Sql query to get range of values

Question

I have a table called TimeList

  SlotID           SlotStartTime    SlotEndTime
  (int identity)   (varchar(10))    (varchar(10))
    1              8:00AM           8:15AM
    2              8:15AM           8:30AM 
    3              8:30AM           8:45AM
    4              8:45AM           9:00AM
    5              9:00AM           9:15AM
    6              9:15AM           9:30AM
    7              9:30AM           9:45AM  
    8              9:45AM           10:00AM

If I am passing SlotStartTime and SlotEndTime I want to get times in between. I used the following query to get timeslots in b/w slotStarttime 8:00AM amd slotEndTime 9:00AM

select * from TimeList1 where StartTime >='8:00AM' and EndTime <= '9:00AM'

Here the result is coming as:

SlotID     SlotStartTime    SlotEndTime
  1          8:00AM            8:15AM
  2          8:15AM            8:30AM 
  3          8:30AM            8:45AM
  8          9:45AM            10:00AM

I want to get slotstarttime starting from 8:00AM and slotendtime ending 9:00AM means expected result is:

SlotID     SlotStartTime    SlotEndTime
  1          8:00AM           8:15AM
  2          8:15AM           8:30AM 
  3          8:30AM           8:45AM
  4          8:45AM           9:00AM

What change do I have to make in my query to get the result as above?

Answer 1

select * from TimeList1 where StartTime >='8:00AM' and EndTime<= '9:00AM'

Less than or equal, not greater than or equal.

Though really, that doesn't work like you expect when your columns are of type varchar. Use a time or datetime type.

The first two paragraphs don't explain a thing as far as I can see. The last paragraph contains a flimsy explanation of the problem. – Jonathan Leffler

In the original post, before you edited it, the OP had as his select statement, select * from TimeList1 where StartTime >='8:00AM' and EndTime >= '9:00AM'

I was responding to that.

My response may be "flimsy", but I wanted to address the obvious problem and get the guy moving forward.

Answer 2

When you're using varchars (or strings in most languages), you'll find that "10:00" < "9:00" just because of character sequencing rules (since "1" < "9").

You should be storing date and time values in date and time columns. If you must use varchars, you'll need to convert them to fixed-size 24-hour format to do it properly (e.g., "01:30", "12:15", "18:25").

But my advice is to store them as proper DB date and time values. Your queries will be easier and faster.

The following solution may get you what you want if I understand your data storage (one entry per quarter hour) but my professional opinion is to fix the column types, since that will allow for far more expressive conditions in your select statements:

select * from TimeList1
where left(StartTime,1) = '8'
and   right(StartTime,2) = 'AM'

Answer 3

Your query is correct. But it's your data types that are wrong.

10:00AM indeed comes before 9:00AM considering they are string, not datetime values.

So if you can't change the data types now, your best luck is to try this query :

Select * from TimeList1
    where StartTime >= Cast('8:00AM' as datetime)
      and EndTime   <= Cast('9:00AM' as datetime)
//make sure StartTime and EndTime is type of datetime

Answer 4

Part of the problem, as has been noted, is that you are using strings to represent times. The other part of your problem is that the AM/PM notation is completely ghastly for computational purposes. Remember, the sequence is:

12:00AM
12:15AM
12:30AM
12:45AM
 1:00AM
 1:15AM
 ...
 9:45AM
10:00AM
10:15AM
 ...
11:30AM
11:45AM
12:00PM
12:15PM
12:30PM
12:45PM
 1:00PM
 1:15PM
 ...

The 24-hour clock has many merits. If you used:

00:00
00:15
00:30
00:45
01:00
01:15
 ...
09:45
10:00
10:15
 ...
11:30
11:45
12:00
12:15
12:30
12:45
13:00
13:15
 ...

(for the same set of numbers) then your VARCHAR strings could be made into CHAR(5) and would automatically sort correctly in time sequence. You would still have to phrase your queries correctly, including the leading zero(es) on times less than 10:00, but the values would sort correctly and compare correctly.

Some DBMS provide support for sub-sets of time. In IBM Informix Dynamic Server, you could use DATETIME HOUR TO MINUTE as the type for the column. That would give you back the flexibility of dropping leading zeroes.

See also Convert 12-hour date/time to 24-hour date/time.

Answer 5

Try this:

SELECT * 
FROM TimeList1 
WHERE (CONVERT(datetime, CONVERT(varchar, GETDATE(), 103) + ' ' + StartTime) >= 
       CONVERT(datetime, CONVERT(varchar, GETDATE(), 103) + ' ' + '8:00AM')) 
    AND 
     (CONVERT(datetime, CONVERT(varchar, GETDATE(), 103) + ' ' + EndTime) <= 
      CONVERT(datetime, CONVERT(varchar, GETDATE(), 103) + ' ' + '9:00AM'))