tag:blogger.com,1999:blog-375697951860081841.post8936265342332920355..comments2008-04-03T18:10:01.160+01:00Jonathanhttp://www.blogger.com/profile/17336586681878234499noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-375697951860081841.post-4918919672198532642008-04-03T18:10:00.000+01:002008-04-03T18:10:00.000+01:00<I>Now, for some magical reason, if you hide the time in the date in your searches, like so:<BR/><BR/> where signupdate between '2008-01-01' and '2008-01-02'<BR/> or<BR/> where signupdate between date('2008-01-01 00:00:00') and date('2008-01-02 00:00:00')<BR/><BR/>then you might not see all the data between the days 01 to 02.</I><BR/><BR/>It's not a magical reason -- if you don't specify the time, the time becomes "00:00:00". This includes implicit conversions, like when you compare a date to a datetime or timestamp.<BR/><BR/>I'd suggest you use "date" when you mean "date" and "time" when you mean "time". "signupdate" is misleading, sounds like it's a date only when it's really a time.<BR/><BR/>If you have a query that specifies "WHERE datetime_col BETWEEN '2008-01-01' and '2008-01-02'" then the implicit conversion is:<BR/><BR/>"WHERE datetime_col BETWEEN '2008-01-01 00:00:00' and '2008-01-02 00:00:00'"<BR/><BR/>Which is why you don't get all the dates you wanted.Sheeri K. Cabralhttp://www.blogger.com/profile/13990877688502800403noreply@blogger.com