CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex
Alex

Reputation: 1733

Microsoft SQL Querying Hours in Timestamp

I'm looking to query a Table that contains timestamps (ex: 2011-05-03 09:52:00).

Need to query the entire table and find timestamps which have been entered between 18:00:00 and 20:00:00 of any day, any month, and any year.

What is the best approach?

Thank you!

Upvotes: 3

Views: 13544

Answers (3)

Nicholas Carey
Nicholas Carey

Reputation: 74385

For SQL Server 2008 (what the OP tagged the question as pertaining to), the obvious way is simple:

select *
from someTable t
where convert(time,t.dtColumn) between '18:00' and '20:00'

the following will work for any version of SQL Server

select *
from someTable t
where t.dtColumn between dateadd(hour,18,convert(datetime,convert(varchar,t.dtColumn,112),112))
                     and dateadd(hour,20,convert(datetime,convert(varchar,t.dtColumn,112),112))

Between is an inclusive test WRT to both the lower and upper bounds of the tested range. To make the test exclusive WRT to one bound or the other, you'll need something like along these lines:

select *
from someTable t
where convert(time,t.dtColumn) >= '18:00'
  and convert(time,t.dtColumn) <  '20:00'

or 

select *
from someTable t
where t.dtColumn >= dateadd(hour,18,convert(datetime,convert(varchar,t.dtColumn,112),112))
  and t.dtColumn <  dateadd(hour,20,convert(datetime,convert(varchar,t.dtColumn,112),112))

Upvotes: 2

M.R.
M.R.

Reputation: 4847

This one will only get the timestamps betwee 18 and 20, including 18:00:00 and 20:00:00


SELECT *     
FROM YourTable     
WHERE CONVERT(varchar(8), YourDateColumn, 108)  between '18:00:00' and '20:00:00'

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

SELECT *
    FROM YourTable
    WHERE DATEPART(hh, YourColumn) BETWEEN 18 AND 20

Upvotes: 7

Related Questions