getdate() and table.depdate compare 1

[codrutza]

Hello
Please help.
I have to retrieve data when table.depdate>currentdate+2 or table.depdate=null
When I say getdate() I get data like
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
2011-07-26 13:21:43.417
and for depdate
2011-07-25 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-26 00:00:00
2011-07-27 00:00:00
2011-07-27 00:00:00
2011-07-27 00:00:00
2011-07-28 00:00:00
2011-07-29 00:00:00
2011-07-29 00:00:00
2011-07-29 00:00:00
which I don’t know to compare or to convert.
If I say
table.depdate>getdate()+2
I get zero records, I guess because getdate() brings the minutes, seconds, …which depdate doesn’t bring.
How shall I write this select?

 

[codrutza]

I think I resolved it
table.depdate>dateadd(dd,1,getdate()) or table.depdate is null

 

[gmmastros]

By adding a day to getdate, you will still have the time component involved. There are various ways to remove the time component of a datetime. My favorite is this:

Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

This works because the DateDiff function returns an integer. So, basically, this code is calculating the whole number of days between 0 (Jan 1, 1900) and GetDate. We then add that number of days to 0 (Jan 1, 1900), resulting in todays date without the time.

So, I would have written this as...

table.depdate >= Select DateAdd(Day, DateDiff(Day, 0, GetDate()), [!]2[/!]) or table.depdate is null

I would have used 2 because you wanted 2 days in the future.

I'm not saying that what you came up with is bad... or wrong... All I'm saying is that I would have done it differently. One benefit of my approach is that the time is NOT involved. If you ever change the app to include a time in the depdate column, your query would then return different data than the one I suggested.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Thank you, George