Inconsistent MySQL date comparison YYYY/MM/DD vs YYYY-MM-DD

Question

I have a MySQL (v 5, MyISAM) query that returns different rows depending on date string format.

(1) IFNULL(date1, ADDDATE('2008/10/31 23:59:59',INTERVAL 1 DAY)) > '2008-10-31 23:59:59'
(2) IFNULL(date1, ADDDATE('2008/10/31 23:59:59',INTERVAL 1 DAY)) > '2008/10/31 23:59:59'

(3) date1 > '2008-10-31 23:59:59'
(4) date1 > '2008/10/31 23:59:59'

'/' vs '-' on RHS of '>' comparisson operator.

(1) 75,098 rows *expected*
(2) 0 rows *DIFFERENCE*
(3) 199 rows *simple case as expected*
(4) 199 rows *simple case as expected*

Question - Why ?

Answer 1

I don't know much about mysql but is it possible that the "/"-based date is interpreted as "YYYY/DD/MM" and so is just completely invalid? In SQL Server I would expect an exception in that case, but if mysql substitutes some kind of "invalid date"-value that could maybe explain the difference.

YYYY-MM-DD is official ISO format. YYYY/MM/DD I have never heard of.

Answer 2

Short answer: Use CAST(... AS DATE)

Long answer:

From MySQL DATE type:

A “relaxed” syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.

In your examples 1 and 2 you're using ADDDATE... this will always return a date with a '-' separator, no matter what the input format:

+-----------------------------------------------+
| ADDDATE('2008/10/31 23:59:59',INTERVAL 1 DAY) |
+-----------------------------------------------+
| 2008-11-01 23:59:59                           |
+-----------------------------------------------+

You're also using IFNULL, which loses the type information, so when you compare this it's comparing as strings.

What you can do is cast this back to a date:

mysql> SELECT IFNULL(null, ADDDATE('2008/10/31 23:59:59',INTERVAL 1 DAY)) > '2008/10/31 23:59:59';
+-------------------------------------------------------------------------------------+
| IFNULL(null, ADDDATE('2008/10/31 23:59:59',INTERVAL 1 DAY)) > '2008/10/31 23:59:59' |
+-------------------------------------------------------------------------------------+
|                                                                                   0 |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT CAST(IFNULL(null, ADDDATE('2008/10/31 23:59:59',INTERVAL 1 DAY)) AS DATE) > '2008/10/31 23:59:59';
+---------------------------------------------------------------------------------------------------+
| CAST(IFNULL(null, ADDDATE('2008/10/31 23:59:59',INTERVAL 1 DAY)) AS DATE) > '2008/10/31 23:59:59' |
+---------------------------------------------------------------------------------------------------+
|                                                                                                 1 |
+---------------------------------------------------------------------------------------------------+

Answer 3

Ref Short answer: Use CAST(... AS DATE)

The LHS is (1) and (2) the same it is the RHS that changes evaluation and that is a string.

Ref "/" based dates.

If '2008/10/31 23:59:59' is an invalid date it would fail in options (1),(3),(4) which are the only one that suceed. If ''2008-10-31 23:59:59' is an invalid date it would fail in options (1), (3) which succeed.