Find records produced in the last hour

问题:

I have a smalldatetime field named myTime recording when the record was created. I need the syntax that selects records created within the last hour.

thought it would be:

and DATEDIFF("hh", datePart(hh, myTime), DatePart(hh, GETDATE()) < 1

where datediff

1. looks for hours
2. looks at the hours portion of the data in myTime as starting
3. looks at the hours portion of now for ending
4. produces an int that gets compared to '1'

the results I'm getting are clearly way, way off but I don't know why.

ADDENDUM: Since both answers essentially agree, the fact that this isn't returning anything for me must trace to how my table's been created. It's created by LogParser working against IIS logs and has date/time info spread across 2 different fields. Date holds just the date info where today's records all look like: 2010-06-08 00:00:00.000 and the Time field looks like: 2010-01-01 15:02:51.000 (the date portion for all records is Jan 01 of 01).

回答1:

Use this:

SELECT  *
FROM    Whatever
WHERE   myTime > DATEADD(HOUR, -1, GETDATE())

回答2:

use SELECT * FROM YourTable WHERE YourDateTime >= DATEADD(hh, -1, GETDATE())

回答3:

If you want whole hours use the following...

--This Hour
SELECT  *
FROM    Whatever
WHERE   myTime >= dateadd(hour, datediff(hour, 0, GETDATE()), 0)

--Last Hour
SELECT  *
FROM    Whatever
WHERE   myTime  < dateadd(hour, datediff(hour, 0, GETDATE()), 0) AND  myTime >= dateadd(hour, datediff(hour, 0,  DATEADD(HOUR, -1, GETDATE())), 0)

--Hour before last
SELECT  *
FROM    Whatever
WHERE   myTime < dateadd(hour, datediff(hour, 0,  DATEADD(HOUR, -1, GETDATE())), 0) AND  myTime >= dateadd(hour, datediff(hour, 0, DATEADD(HOUR, -2, GETDATE())), 0)

回答4:

Although this WHERE myTime > DATEADD(HOUR, -1, GETDATE()) should have worked for me, for some strange reason the datetime feild in my sql server is configured in a strange way where the above code would return the entire day, not just the hour prior to Now. After some troubleshooting I found that GETDATE() was actually 7 hours ahead of the current time so my query looks like this:

WHERE myTime BETWEEN DATEADD(HH, 6, GETDATE()) AND DATEADD(HH, 7, GETDATE())

So DATEADD(HH, 7, GETDATE() is now plus 7 hours (which comes out being the current time according to the db). And then subtract an hour to get all rows within that hour block. I thought I should post this just to help anyone having the same issue.