CODEWITHSUNDEEP
sql-server-2008t-sql
Amarundo
Amarundo

Reputation: 2397

SELECT statement filtering by time of day

Table A, columns OrderId, OrderTimeStamp (datetime).

I want to SELECT all records for any date, but between 10 am and 1 pm, for example.

How do I do that?

Thanks!

Upvotes: 5

Views: 28783

Answers (4)

David Peden
David Peden

Reputation: 18474

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

update:

doh, ntziolis beat me by 30 seconds. one thing to note, if you want 1pm included, be sure to make the last part of the where <=. if you want to only go up to 12:59.999 pm < is appropriate.

Upvotes: 2

ntziolis
ntziolis

Reputation: 10231

In 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 a format.

For further info see here:
http://msdn.microsoft.com/en-us/library/ms174420.aspx

UPDATE

Since ouy are working with SQL 2008, you can make use of the TIMEdata type and make your query much simpler (and correct as well): 

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

For futher info see here:
http://msdn.microsoft.com/en-us/library/bb677243.aspx

Upvotes: 4

Diego
Diego

Reputation: 36176

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

if you need to filter by minutes (like 13:20 and 14:15), try the suggestions on this link

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44356

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'

Upvotes: 21

Related Questions