Question about DateAdd Function and Information

Question

I am currently writing some SQL and have run into an odd problem.

Used code:

select * from #table1 where datefield = DATEADD(day, -2, getdate()) 

The problem I am facing will not output such information. If I add '>' to '=', it will pull out the information. If I put the exact date in the request instead of "dateadd", it pulls out the correct entries.

I tested the date parameters, and there are corresponding entries for 4/10/2011. But for some reason it just doesn't like '= dateadd' in this case.

I also tried using the string 'current_timestamp' instead of 'getdate' also with the same results.

Any suggestions?

Answer 1

getdate() 1/300 second exact. You are requesting the exact time to (almost) milliseconds.

If your date field is DATETIME , but without the actual time parts, use this:

 SELECT * FROM #table1 WHERE datefield = DATEADD(day, -2, CAST(getdate() AS DATE)) 

or better is:

 SELECT * FROM #table1 WHERE datefield >= DATEADD(day, -2, CAST(getdate() AS DATE)) AND datefield < DATEADD(day, -1, CAST(getdate() AS DATE)) 

The last request will return you all records for the date (even with the set time part).

Answer 2

DATEADD(day, -2, getdate()) returns a time component, e.g. 2011-04-10 16:35:23.437

You can use DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(day, -2, getdate()))) to get only part of the date if your version of the Sql server is less than 2008 (when the DATE type was added)

Answer 3

Getdate() returns a Datetime , which includes the temporary part. I assume you agree with the date. Try:

where datefield = CAST(DATEADD(day, -2, getdate())) as DATE)