T-SQL (date) - how to get value within one hour?

Question

I am looking for an optimal decision of how to get table values according to their date and time but within just ONE past hour.

I mean something in this way (a pseudocode):

 SELECT value FROM Table WHERE date BETWEEN getdate() AND getdate()-ONE_HOUR

For the purpose of this question Table has these columns:

• value
• date

Any useful snippet is appreciated :)

Solution 1

SELECT Value
FROM Table
WHERE Date between dateadd(hour, -1, getdate()) and getdate()

Description of the DATEADD function:

DATEADD (datepart , number , date )

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

datepart     Abbreviations  
-----------  -------------
year         yy, yyyy
quarter      qq, q
month        mm, m
dayofyear    dy, y
day          dd, d
week         wk, ww
weekday      dw, w
hour         hh 
minute       mi, n
second       ss, s
millisecond  ms 
microsecond  mcs 
nanosecond   ns 

More information:

• DATEADD (Transact-SQL)

Solution 2

Something like this should work.

SELECT value
FROM Table 
WHERE date >= dateadd(hour,-1,getdate())
   and date <= getdate()

Comments

[HABO]

As a rule, it is best to invoke GetDate() once and save the value in a variable, then use the variable as needed. It avoids all sorts of nasties around midnight.

[The Evil Greebo]

Comment retraced. I'm not sure whether >= and <= or between is more efficient though... one day I'll have to test.

[Martin Smith]

@TheEvilGreebo - No difference what so ever. The predicate will appear as >= and <= in the plan anyway.

[user592704]

It is interesting. Thanks. And what if I use const like -3 in this function? Will it give me past 3 hours value result then?

[The Evil Greebo]

Affirmative. The number value (param 2) is added to (or with a negative value, subtracted) the date value (param 3). If you used day instead of hour, it'd subtract 1 (in my example) days instead of 1 hours, and a -3 would make it subtract 3 days.