From: Date: January 6 2006 8:59am
Subject: Problem with datetime value
List-Archive: http://lists.mysql.com/mysql/193576
Message-Id: <43BE236B.7070508@binasanprima.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi All,

I got a problem with a query that involved datetime field.
the table structure goes something like this :

CREATE TABLE `price_log` (                                      
  `Item` char(20) NOT NULL default '',                          
  `Started` datetime NOT NULL default '0000-00-00 00:00:00', 
  `Price` decimal(16,3) NOT NULL default '0.000',                 
  PRIMARY KEY  (`Item`,`Started`)                     
) ENGINE=MyISAM;   

insert into price_log values
("A1","2005-11-01 08:00:00",1000),
("A1","2005-11-15 00:00:00",2000),
("A1","2005-12-12 08:00:00",3000),
("A2","2005-12-01 08:00:00",1000);

when i execute this query :

select *
from price_log
where
item like "A%"
and started<="2005-12-01 24:00:00";

it will return this result set :
Item    Started               Price 
------  -------------------  --------
A1      2005-11-01 08:00:00  1000.000
A1      2005-11-15 00:00:00  2000.000
A2      2005-12-01 08:00:00  1000.000


but, when i limited to certain item, like this :

select *
from price_log
where
item="A1"
and started<="2005-12-01 24:00:00";

it return an empty set.

What is going wrong?
I know it should return 2 rows, as the previous sql result set got two 
rows of item 'A1'.

is it because the value of "2005-12-01 24:00:00" which ofcourse is not a 
valid date time value.
but anyhow, the first query was succeded ??

im using mysql 4.1.11, 4.1.15, 4.0.1 .. and the result were all the same.

Thanks for any comment.


-
Leo