9

Is there a way in TSQL to query for rows where a datetime column's value is "now minus (a certain amount of time)" to select a time range?

For example in MySQL I could build this predicate like:

(EndTime BETWEEN NOW() - INTERVAL 2 DAY AND NOW())

How can this be done in TSQL in SQL Server?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Kyle Brandt
  • 26,938
  • 37
  • 124
  • 165

3 Answers3

19

You would use the dateadd function, e.g.

where endtime between dateadd(day, -2, getdate()) and getdate()

Because the column endtime is not a function parameter, the query optimizer can use any indexes that might be defined on it (the definition of sargable).

Community
  • 1
  • 1
Bobby D
  • 2,129
  • 14
  • 21
4

If you are trying to subtract two days from today, you can do

DateAdd( d, -2, CURRENT_TIMESTAMP)

DATEADD (Transact-SQL)

Thomas
  • 63,911
  • 12
  • 95
  • 141
3

You can use the datediff function.

For example, to find things created within the last five minutes, you could execute

select *
from   Table t
where  datediff(minute, t.CreationDate, getutcdate()) <= 5
Jarrod Dixon
  • 15,727
  • 9
  • 60
  • 72
  • 1
    Although simple, it seems like this approach would not be able to take advantage of index information (if it existed). But RDBMS optimizations are PFM to me. –  Mar 29 '11 at 19:59
  • @pst - you are right, `datediff` isn't [sargable](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable/799616#799616). [Bobby D's answer](http://stackoverflow.com/questions/5477653/tsql-now-minus-a-certain-amount-of-time/5477688#5477688) is more appropriate. – Jarrod Dixon Mar 29 '11 at 20:10