Querying datetime columns for 'Now' minus a certain amount of time?

Question

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?

Answer 1

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

Answer 2

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

DateAdd( d, -2, CURRENT_TIMESTAMP)

DATEADD (Transact-SQL)