select Data between two date range mysql return wrong values 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?
Tip:  Test uses of functions:

Your first use of str_to_date:

mysql&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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)