MySQL 4.0.13 - Date Comparison Issue

------=_NextPart_000_004F_01C3515A.E49592A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi people,

Newbie to to the list here, so bare with me. I'm not sure if this is a =
bug, but I figured I should atleast question it and post it on here for =
all to have a look at.

I upgraded to MySQL 4.0.13 few months back (from latest version of 3) - =
very few problems at all, until I noticed this particular quirk - I will =
make use of the SQL query below as an example:

--
select domain, sid, period, cdate, cdate + interval period year as =
xdate, renew, to_days(cdate + interval period year)-to_days(now()) from =
dns having xdate < now() order by xdate desc
--

What appears to happen here is - mysql returns all the rows of that =
query where the condition xdate <=3D now(), where as this is not =
actually correct - the query actually states: xdate < now() (xdate =
STRICTLY less than now(), and NOT equal to). For example, it returns a =
row if xdate=3D'2003-07-22' and now()=3D'2003-07-22' - but there is no =
'=3D' sign in the query, so this is wrong.

This query ran perfectly in MySQL 3 - so, I'm guessing this is a MySQL 4 =
quirk? Looks to me like a date comparison issue - has anyone else had =
similar issues with MySQL 4.0.13 and date comparisons?

Any feedback much appreciated.

Regards...

--
Mark
------=_NextPart_000_004F_01C3515A.E49592A0--

Re: MySQL 4.0.13 - Date Comparison Issue

Hi!

On Jul 23, Mark Hedges wrote:
> Hi people,

> For example, it returns
> a row if xdate='2003-07-22' and now()='2003-07-22' - but there is no
> '=' sign in the query, so this is wrong.

Try simply

SELECT NOW()

you'll see that NOW() is a timestamp, something like
"2003-07-22 12:13:14"

so, if compared, "2003-07-22 12:13:14" > "2003-07-22".

You need to use CURDATE() instead of NOW().

See also http://bugs.mysql.com/bug.php?id=889

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MySQL 4.0.13 - Date Comparison Issue

Mark Hedges writes:
> Hi people,
>
> Newbie to to the list here, so bare with me. I'm not sure if this is a bug, but I figured I should atleast question it and post it on here for all to have a look at.
>
> I upgraded to MySQL 4.0.13 few months back (from latest version of 3) - very few problems at all, until I noticed this particular quirk - I will make use of the SQL query below as an example:
>
> --
> select domain, sid, period, cdate, cdate + interval period year as xdate, renew, to_days(cdate + interval period year)-to_days(now()) from dns having xdate < now() order by xdate desc
> --
>
> What appears to happen here is - mysql returns all the rows of that query where the condition xdate <= now(), where as this is not actually correct - the query actually states: xdate < now() (xdate STRICTLY less than now(), and NOT equal to). For example, it returns a row if xdate='2003-07-22' and now()='2003-07-22' - but there is no '=' sign in the query, so this is wrong.
>
> This query ran perfectly in MySQL 3 - so, I'm guessing this is a MySQL 4 quirk? Looks to me like a date comparison issue - has anyone else had similar issues with MySQL 4.0.13 and date comparisons?
>
> Any feedback much appreciated.
>
> Regards...
>
> --
> Mark

Your problem is quite simple.

NOW() returns full datetime, which means that both date and time
count, not just date.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org