Help with Date in Where Clause

--000325573422846d1f049b2a073d
Content-Type: text/plain; charset=ISO-8859-1

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

--000325573422846d1f049b2a073d--

Re: Help with Date in Where Clause

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)=3D'2011-01-31';

=2D-=20
J=F8rn 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Help with Date in Where Clause

--90e6ba4fc486fbb069049b2a3cbc
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thank you very much J=F8rn

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=F8rn Dahl-Stamnes
wrote:

> J=F8rn

--90e6ba4fc486fbb069049b2a3cbc--

Re: Help with Date in Where Clause

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=gcdmg-mysql-2@m.gmane.org

Re: Help with Date in Where Clause

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=gcdmg-mysql-2@m.gmane.org