Find records produced in the last hour

Question

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

Answer 1

Use this:

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

Answer 2

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)

Answer 3

Use this:

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