Re: Help with Date in Where Clause

2011-01-31 Thread Bruce Ferrell 
On 01/31/2011 12:18 PM, Jørn Dahl-Stamnes wrote:
> On Monday 31 January 2011 21: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.
>> 
> select * from your_table where convert(dateAdded, date)='2011-01-31';
>
>   
not so good, but it works:

select * from your_table where dateAdded like '2011-01-31%';

OR
select * from your_table where dateAdded between '2011-01-30%' and
'2011-01-31%';


better:

select * from your_table where DATE_SUB('2011-01-31', INTERVAL 1 DAY);

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with Date in Where Clause

2011-01-31 Thread Shawn Green (MySQL) 

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help with Date in Where Clause

2011-01-31 Thread Phillip Baker 
Thank you very much Jørn

Blessed Be

Phillip

"Never ascribe to malice what can be explained by incompetence"
-- Hanlon's Razor


On Mon, Jan 31, 2011 at 1:18 PM, Jørn Dahl-Stamnes
wrote:

> Jørn

Re: Help with Date in Where Clause

2011-01-31 Thread Jørn Dahl-Stamnes 
On Monday 31 January 2011 21: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.

select * from your_table where convert(dateAdded, date)='2011-01-31';

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Help with Date in Where Clause

2011-01-31 Thread Phillip Baker 
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