Simple Date/TIme Question

[mlocurci]

I really hate dealing with dates and times in queries. And again, I find myself pulling my hair our.

I need to find all completedates for yesterday, so i want from 12 Midnight to 12 Midnight. How can I do that? Here is what I am using, but its wrong. How do I normalize to

2008-06-24 00:00:00.000 to 2008-06-24 23:59:59.999

WHERE ((MESSAGESENDERCATEGORYVIEW.CompleteDATE >=getdate()-1)and (MESSAGESENDERCATEGORYVIEW.CompleteDATE <getdate())

 

[jbenson001]

Give this a try:

WHERE MESSAGESENDERCATEGORYVIEW.CompleteDATE >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) AND
      MESSAGESENDERCATEGORYVIEW.CompleteDATE <  DateAdd(Day, DateDiff(Day, 0, GetDate() + 1 ), 0)

 

[SQLSister]

http://www.tek-tips.com/faqs.cfm?fid=6628

"NOTHING is more important in a database than integrity." ESquared