Geek asks and answers

Microsoft SQL Querying Hours in Timestamp

I am looking for a query for a table containing timestamps (for example: 2011-05-03 09:52:00).

You must query the entire table and find the timestamps that were entered between 18:00 and 20:00:00 of any day, any month and any year.

What is the best approach?

Thank!

+3
source share
3 answers
SELECT *
    FROM YourTable
    WHERE DATEPART(hh, YourColumn) BETWEEN 18 AND 20
+7
source

This one will only receive timestamps between 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'
+3
source

SQL Server 2008 ( OP , ), :

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

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 WRT , . WRT , - :

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


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

More articles:


All Articles