CODEWITHSUNDEEP
mysqlsqldatetimedate-comparison
Lance
Lance

Reputation: 3213

msql date comparison working ONLY if the year is is first

I have a that is doing a date comparison in the where clause which only works if the year is the first part of the string. the crDate field is a DATETIME so shouldn't both of the following work?

and     crDate between '2009/01/01' and '2009/01/21'

and     crDate between '01/01/2009' and '01/21/2009'

if seems to me that both these statements are the same but the first one returns the expected result and the second one returns 0 records.

Is there a setting in mySQL that could get turned off or turned on that would make these function differently?

Upvotes: 0

Views: 104

Answers (1)

Álvaro González
Álvaro González

Reputation: 146460

MySQL is not smart enough to parse all possible date formats. Even people are not smart enough—is 10/01/2012 Jan 10 or Oct 1? You must perform an appropriate conversion from string or rely on the default format, which is normally YYY-MM-DD:

mysql> SELECT @@date_format;
+---------------+
| @@date_format |
+---------------+
| %Y-%m-%d      |
+---------------+

Upvotes: 1

Related Questions