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

ASKER

It's not a timestamp field... sorry for the confusion
Just a DATETIME
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
WHERE
    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

ASKER

But I have many dates!



What about this...
First

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

then

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

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