[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-bugs
Subject:    Re: MySQL 4.0.13 - Date Comparison Issue
From:       Sinisa Milivojevic <sinisa () mysql ! com>
Date:       2003-07-23 21:12:21
[Download RAW message or body]

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 <sinisa@mysql.com>
 / /|_/ / // /\ \/ /_/ / /__   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=mysql-bugs@progressive-comp.com


[prev in list] [next in list] [prev in thread] [next in thread]