Thanks for the replies all (and for the blog link - one to add to my feeds I think).
Yes I spotted that adding the leading zero to the month yields the correct result here, but I think I know why. If you imagine BETWEEN using a string comparison here then the results for with and without leading zero are both correct; but the documentation explicitly states that for a comparison, timestamp conversions are used and this appears not to be the case :-( Perhaps I should pose this to the internals list...? Rob On 5/29/07, Fred Ballard <[EMAIL PROTECTED]> wrote:
Everything seems to go fine for me if I change the two 2007-3-23 to 2007-03-23. Fred -----Original Message----- From: Rob Desbois [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 5:02 AM To: mysql@lists.mysql.com Subject: datetime type conversion problem I am having issues with type conversion not working as expected per the documentation. I am using in MySQL 5.0.27 for x86/Windows. The documentation at http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html states that for comparison operators "If one of the arguments is a TIMESTAMP or DATETIMEcolumn and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed." So the following SQL statement should convert the last string to a timestamp then perform date-time comparison: > SELECT '2007-05-24 00:00:00' BETWEEN > ('2007-3-23' - INTERVAL 24 HOUR) AND '2007-3-23 23:59:59'; Thus it should return 0 as 24th May is not in the given range. I get the result 1 however... If I perform an explicit conversion using CAST('2007-3-23 23:59:59' AS DATETIME) I get 0 as expected. Can anyone enlighten me - have I misunderstood something somewhere? Thanks, --rob
-- Rob Desbois Eml: [EMAIL PROTECTED] Tel: 01452 760631 Mob: 07946 705987 "There's a whale there's a whale there's a whale fish" he cried, and the whale was in full view. ...Then ooh welcome. Ahhh. Ooh mug welcome.
