Link to home
Start Free TrialLog in
Avatar of JElster
JElsterFlag for United States of America

asked on

sql server - how to query specific time - T SQL

I have a timestamp field.
I need to query all the records for the hour.. A record for 1,2,3 o'clock.
The data looks like

2012-11-01 01:00:00
2012-11-01 01:15:00
2012-11-01 01:45:00
2012-11-01 02:00:00
2012-11-01 02:15:00
2012-11-01 03:00:00

How do query for the full hour?
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

This will give you the basic structure of the query:

select * from your_table
where column_name between convert(datetime, '2012-11-01 01:00:00', 120) and convert(datetime, '2012-11-01 02:00:00', 120)
Just so you understand, a TimeStamp field is not the same as a Date/Time field.  According to Microsoft:

"It is used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type."
Avatar of JElster


It's not a timestamp field... sorry for the confusion
Most important are:
1) not to perform any function on the column itself
2) not to count the same row more than once

I strongly recommend the style below for ALL date/datetime checks:

--query for 1AM rows only
    datetime_column >= '20121101 01:00:00' AND
    datetime_column <  '20121101 02:00:00'

Leaving out the dashes prevents any conversion errors ('YYYYMMDD' is always interpreted correctly) and/or misunderstandings about what the date value represents.

Using >= and < rather than between avoids counting rows on the exact hour twice.
Avatar of JElster


But I have many dates!

What about this...

SUBSTRING( CONVERT(nvarchar(30), TIMESTAMP, 120),15,5) AS DT


select ....   WHERE DT = '00:00'
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial