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)
Dale Fye
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."
JElster
ASKER
It's not a timestamp field... sorry for the confusion
Just a DATETIME
Your help has saved me hundreds of hours of internet surfing.
fblack61
Scott Pletcher
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.
JElster
ASKER
But I have many dates!
What about this...
First
SUBSTRING( CONVERT(nvarchar(30), TIMESTAMP, 120),15,5) AS DT
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
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)