0

I want to select data between 9/Nov/2018 and 11/Nov/2018, but the result seems wrong - it returned data outside the two ranges. Using the below query...

select * 
from radacct 
where DATE_FORMAT(radacct.AcctStartTime,'%d %M %Y') 
    between STR_TO_DATE('2018-11-9','%d %M %Y') 
        AND STR_TO_DATE('2018-11-11','%d %M %Y');      

...I got back data like this...


+-------+-----------------------+-----------------------+-----------------------------+
| 12463 |  2018-10-11 16:22:21  |  2018-10-11 17:23:07  |        2018-10-11 16:22:21  |
+-------+-----------------------+-----------------------+-----------------------------+
| 12523 |  2018-10-11 17:16:01  |  2018-10-11 17:16:10  |       2018-10-11 17:16:01   |
+-------+-----------------------+-----------------------+-----------------------------+

...as you see, the date is 2018-10-11. How did this happen?

  • Explain, why you convert field value to string whereas literals to date? in WHERE clause... – Akina Nov 9 '18 at 9:00
1

Tip: Test uses of functions:

Your first use of str_to_date:

mysql> SELECT STR_TO_DATE('2018-11-9','%d %M %Y');  SHOW WARNINGS;
+-------------------------------------+
| STR_TO_DATE('2018-11-9','%d %M %Y') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2018-11-9' for function str_to_date |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Aha -- It's year-month-day, not day-month-year:

mysql> SELECT STR_TO_DATE('2018-11-9','%Y %M %d');  SHOW WARNINGS;
+-------------------------------------+
| STR_TO_DATE('2018-11-9','%Y %M %d') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2018-11-9' for function str_to_date |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Wait, m, not M:

mysql> SELECT STR_TO_DATE('2018-11-9','%Y %m %d');  SHOW WARNINGS;
+-------------------------------------+
| STR_TO_DATE('2018-11-9','%Y %m %d') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2018-11-9' for function str_to_date |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Oh, yeah, the dashes need to be specified:

mysql> SELECT STR_TO_DATE('2018-11-9','%Y-%m-%d');
+-------------------------------------+
| STR_TO_DATE('2018-11-9','%Y-%m-%d') |
+-------------------------------------+
| 2018-11-09                          |
+-------------------------------------+
1 row in set (0.00 sec)

But, anyway, you can simply use the string '2018-11-9', no conversion needed!

Or, fix the string a different way (now needing slashes, but M and order are correct:

mysql> SELECT STR_TO_DATE('9/Nov/2018','%d/%M/%Y');
+--------------------------------------+
| STR_TO_DATE('9/Nov/2018','%d/%M/%Y') |
+--------------------------------------+
| 2018-11-09                           |
+--------------------------------------+
1 row in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.