Ah ok I rechecked the documentation for BETWEEN which includes additional information not mentioned on the type conversion page:
For best results when using BETWEEN with date or time values, you should use
CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.
Doesn't necessarily explain why it doesn't work but does suggest the fix. Thanks all for your input -- I've added a note to the type conversion page to help other distraught users :-) --rob On 5/29/07, Rob Desbois <[EMAIL PROTECTED]> wrote:
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.
-- 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.
