Clever Geek Handbook

What's wrong with the MySQL DATE () function?

I had some bizarre results from queries that I tested with a function DATE

that culminates in these little beauties:

mysql> SELECT id FROM job WHERE DATE(due)=CURRENT_DATE;
Empty set (0.00 sec)

mysql> SELECT id FROM job WHERE DATE(due)=CURRENT_DATE AND id>2022;
Empty set (0.00 sec)

mysql> SELECT id FROM job WHERE DATE(due)=CURRENT_DATE AND id=2023;
+------+
| id   |
+------+
| 2023 | 
+------+

and for a little more comedy

mysql> SELECT id, DATE(due) FROM job WHERE DATE(due) IS NULL AND id>2022;

gives us:

+------+------------+
| id   | DATE(due)  |
+------+------------+
| 2023 | 2009-08-24 | 
| 2024 | 2009-08-24 | 
| 2025 | NULL       | 
| 2026 | 2009-08-24 | 
| 2027 | NULL       | 
| 2032 | NULL       | 
| 2031 | NULL       | 
| 2033 | NULL       | 
| 2034 | NULL       | 
| 2035 | NULL       | 
| 2036 | NULL       | 
| 2037 | NULL       | 
| 2038 | NULL       | 
+------+------------+

it's on 5.0.45

Is the function DATE()

completely unreliable or am I missing something?

Clarifications:

The corresponding field is of type datetime

, and there was no date rollover in between requests. All of the above queries still give the same results, and NOW()

currently2009-08-24 22:54:17

In response to Eric's request:

mysql> SELECT id, due, DATE(due) FROM job WHERE id>2022;
+------+---------------------+------------+
| id   | due                 | DATE(due)  |
+------+---------------------+------------+
| 2023 | 2009-08-24 00:00:00 | 2009-08-24 | 
| 2024 | 2009-08-24 17:20:56 | 2009-08-24 | 
| 2025 | NULL                | NULL       | 
| 2026 | 2009-08-24 17:22:07 | 2009-08-24 | 
| 2027 | NULL                | NULL       | 
| 2032 | NULL                | NULL       | 
| 2031 | NULL                | NULL       | 
| 2033 | NULL                | NULL       | 
| 2034 | NULL                | NULL       | 
| 2035 | NULL                | NULL       | 
| 2036 | NULL                | NULL       | 
| 2037 | NULL                | NULL       | 
| 2038 | NULL                | NULL       | 
+------+---------------------+------------+
+2


source to share


2 answers


It looks like the function is TO_DAYS

much more robust for my purposes - direct replacement DATE

for TO_DAYS

seems to be going well.



0


source


This worked for me:

SELECT id FROM (SELECT * FROM job WHERE due IS NOT NULL) job_not_null WHERE DATE(due) = CURRENT_DATE


I was able to reproduce the behavior where the comparison failed after the first NULL value in the column.

+1


source






More articles:


All Articles