I'm not sure specifically why it's acting the way it is, but it looks
like it's converting your date strings slightly differently depending on
how you use them.
If you properly pad the month out in the two strings it seems to work.
SELECT '2007-05-24 00:00:00' BETWEEN ('2007-03-23' - INTERVAL 24 HOUR)
AND '2007-03-23 23:59:59';
Chris
Rob Desbois wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
