Bug #1646 BETWEEN and DATETIME type comparison fails

• Submitted: 24 Oct 2003 8:32
• Modified: 24 Oct 2003 10:24
• Reporter: Adam Hunger
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.13-standard
• OS: FreeBSD (FreeBSD)
• CPU Architecture: Any

[24 Oct 2003 8:32] Adam Hunger

Description:
Can not reliably use BETWEEN to compare datetime fields.  Appears to use standard string comparison before converting to internal type for datetime and then comparing.

(Yes, I did search the bugs database for any bug in any state with the word BETWEEN)

How to repeat:
SELECT cast(  '2003-01-20' AS datetime ) 
BETWEEN cast(  '2003-1-01' AS datetime )  AND cast(  '2003-2-02' AS datetime ) 

returns 0

SELECT unix_timestamp(  '2003-01-20'  ) 
BETWEEN unix_timestamp(  '2003-1-01'  )  AND unix_timestamp(  '2003-2-02'  ) 

returns 1

Suggested fix:
I use the standard >= and < comparisons when comparing datetime data.  Between would simply be more convienent and possibly more efficient.

[24 Oct 2003 10:24] Indrek Siitan

This is expected behaviour. A quote from the manual page about casting
(http://www.mysql.com/doc/en/Cast_Functions.html):

NOTE: In MysQL 4.0 the CAST() to DATE, DATETIME, or TIME only marks 
the column to be a specific type but doesn't change the value of the column.  

'2003-1-01' and '2003-2-02' are not valid date values, so the query
returns 0. If you change them to correct date values ('2003-01-01' and
'2003-02-02'), the query works as expected.