My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Add to Memories Share Next Entry
str_to_date can be a little ambiguous
swanhart
The MySQL manual for str_to_date states:
If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An
illegal value also produces a warning.

Surely "I'm_not_a_valid_date" is not a valid date, time or datetime value.
mysql> select str_to_date("I'm_not_a_valid_date","I'm_not_a_valid_date");
+------------------------------------------------------------+
| str_to_date("I'm_not_a_valid_date","I'm_not_a_valid_date") |
+------------------------------------------------------------+
| 0000-00-00                                                 |
+------------------------------------------------------------+
1 row in set (0.00 sec)


The problem here is that values in the format string which are not preceded by the percent sign (%) are treated as constant characters which must match the input string exactly. Normally these characters are used as delimiters. For example, the dash characters in the format string '%Y-%m-%d'.

For example:
mysql> select str_to_date('2008?01?01','%Y?%m?%d');
+--------------------------------------+
| str_to_date('2008?01?01','%Y?%m?%d') |
+--------------------------------------+
| 2008-01-01                           |
+--------------------------------------+
1 row in set (0.00 sec)


The ambiguity comes into play when both strings consist ONLY of matching characters with NO format specifications:
mysql> select str_to_date('2008?01?01','2008?01?01');
+----------------------------------------+
| str_to_date('2008?01?01','2008?01?01') |
+----------------------------------------+
| 0000-00-00                             |
+----------------------------------------+
1 row in set (0.00 sec)


I think this should return NULL, because no format characters have been specified, therefore no legal date should be output.

I've filed Bug 44891 to let MySQL decide.
Tags: ,

I do agree that this should return NULL. MySQL staff seems to be taking an easier root of changing the documentation instead of fixing the bug.

You are viewing swanhart