On 1/31/2011 15:12, Phillip Baker wrote:
> Greetings All,
>
> I am looking for a little help in setting a where clause.
> I have a dateAdded field that is a DATETIME field.
> I am looking to pull records from Midnight to midnight the previous day.
> I thought just passing the date (without time) would get it but I keep
> getting an empty record set.
> So looking for something that works a bit better.
>
> Any suggestions?
>
> Blessed Be
>
> Phillip
>
> "Never ascribe to malice what can be explained by incompetence"
> -- Hanlon's Razor
>
All of the datetime values for "yesterday" actually exist as a range of
datetime values between midnight that morning (inclusive) and midnight
the next morning (not part of the search). So your WHERE clause needs to
resemble
... WHERE dtcolumn >= '2011-01-21 00:00:00' and dtcolumn < '2011-01-22
00:00:00'
This pattern has the added advantage of not eliminating the possibility
of using an INDEX on the dtcolumn column by wrapping it inside a function.
Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
