get the data of last three hours inserted in the table

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

No they won't be skipped because we're checking for DATEDIFF < 3.

Zero is less than 3. ;)

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')

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

 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')

 

 

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

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()) < 180