List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:January 31 2011 8:39pm
Subject:Re: Help with Date in Where Clause
View as plain text  
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
Thread
Help with Date in Where ClausePhillip Baker31 Jan
  • Re: Help with Date in Where ClauseJørn Dahl-Stamnes31 Jan
    • Re: Help with Date in Where ClausePhillip Baker31 Jan
    • Re: Help with Date in Where ClauseBruce Ferrell31 Jan
  • Re: Help with Date in Where ClauseMySQL)31 Jan