Filtering SELECT statements by time of day

Question

Table A, columns OrderId, OrderTimeStamp (datetime).

I want to select all entries for any date, but between 10:00 and 13:00, for example.

How to do it?

Thanks!

Answer 1

declare @t table(d datetime) insert @t values('2012-01-01 09:00'),('2012-01-01 10:00'),('2012-01-01 11:00') select cast(d as time) from @t where cast(d as time) between '10:00' and '13:00' 

Answer 2

The T-SQL DatePart will do the trick:

To get all records from 10:00 - 12:59 :

 SELECT * FROM TableA WHERE DATEPART(hh, [OrderTimeStamp]) >= 10 AND DATEPART(hh, [OrderTimeStamp]) < 13 

Or if you want to get all records from 10:00 - 13:00 (seconds / milliseconds are omitted):

 SELECT * FROM TableA WHERE DATEPART(hh, [OrderTimeStamp]) >= 10 AND DATEPART(hh, [OrderTimeStamp]) < 13 OR (DATEPART(hh, [OrderTimeStamp]) = 13 AND DATEPART(mi, [OrderTimeStamp]) = 0) 

Keep in mind that 24h values ​​are returned from the DatePart function when used with hh as the format.

See here for more details:
http://msdn.microsoft.com/en-us/library/ms174420.aspx

UPDATE

Since ouy works with SQL 2008, you can use the TIME data type and make your query a lot easier (and right too):

 SELECT * FROM TableA WHERE CONVERT(TIME(7), [OrderTimeStamp ]) >= '10:00:00.0000000' AND CONVERT(TIME(7), [OrderTimeStamp ]) <= '13:00:00.0000000' 

See here for more details:
http://msdn.microsoft.com/en-us/library/bb677243.aspx

Answer 3

 select * from TableA where datepart(hh, OrderTimeStamp) >= 10 and datepart(hh, OrderTimeStamp) < 13 

update:

doh, ntziolis beat me for 30 seconds. one note, if you want to include 1pm, be sure to do the last part where <=. if you want to go only until 12: 59.999 p.m. <suitable.

Answer 4

 select * from tableA where datepart(hh, OrderTimeStamp) between 10 and 13 

if you need to filter by minutes (e.g. 13:20 and 14:15), try the sentences