get the data of last three hours inserted in the table

Question

Hi I want to get the data of last three hours inserted in the table i wan to know how i will subtract three hours. for eg: select * from table_1 where date_time between CURRENT_TIMESTAMP and ... Any help?

John Penn · Answer

select * from table_1 where DATEDIFF(hh date_time GETDATE())

Vijay Yadav · Answer

John and Suthish thanks a lot!!!! It was like a dilemma for me.. anyway :)

Suthish Nair · Answer

ya.. then it will works on a condition.

John Penn · Answer

No they won't be skipped because we're checking for DATEDIFF Zero is less than 3. ;)

Suthish Nair · Answer

So in my case the query will skip those rows having above date and time because the hours returning zero? So how to handle this situation. I think its important to Vijay also to know the work around.

John Penn · Answer

The same is true when you use DATEDIFF to see how many months have elapsed between two datetimes: // returns 1 because a month boundary was crossed select DATEDIFF(mm '2011-03-31 12:00:00' '2011-04-01 12:00:00') // returns 0 because a month boundary was not crossed select DATEDIFF(mm '2011-04-01 12:00:00' '2011-04-01 12:00:00')

Vijay Yadav · Answer

Hmmm....

John Penn · Answer

In that case the result is 0 because no boundary has been crossed between the two datetimes.

Suthish Nair · Answer

One of the confusing and complicated part is Date and Time.. What happen if this scenario comes not possible but suppose.. select DATEDIFF(HH '2011-03-31 12:00:00' '2011-03-31 12:00:00')

Vijay Yadav · Answer

Thanks John It was a good suggestion i would use minutes.. :)

John Penn · Answer

One thing to watch out for with DATEDIFF is that it counts the number of boundaries crossed between the two timestamps which may not give you the results you're expecting. For example if you run this: select DATEDIFF(hh '2011-03-31 11:59:00' '2011-03-31 12:01:00') You might expect it to return 0 because there's only a 2 minute difference between those timestamps. Actually it will return 1 because an hour boundary (12:00) was crossed between those two. To make your select more granular you might want to use minutes instead of hours: select * from table_1 where DATEDIFF(mi date_time GETDATE())

Vijay Yadav · Answer

Hey John Thanks once again Man.. :)