N
N
Newto2021-04-15 11:31:34
MySQL
Newto, 2021-04-15 11:31:34

How can this be. Different result for "less than or equal to" and "BETWEEN" for the same date?

Good afternoon. There is a MySQL database, there is a query, in part of which is this:

AND `info`.`created` <= STR_TO_DATE('2021-04-14 23:59:59', '%Y-%m-%d %H:%i:%s')


Where `info`.`created` is of type DATETIME. When issuing, a result appears in which 10 lines with the value `info`.`created`=2021-04-14 XX:XX:XX appear. Well, the rest of the lines with earlier dates, they do not interest us in the context of this issue. What, in fact, is the question: if I change the query to

AND `info`.`created` BETWEEN STR_TO_DATE('2021-04-14 00:00:00', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2021-04-14 23:59:59', '%Y-%m-%d %H:%i:%s')

Those. if I request all the lines with the date 2021-04-14, then the lines where `info`.`created`=2021-04-14 XX:XX:XX will no longer be 10, but 13.

Can anyone tell me how this is even possible?

UPD: The essence of the question is not that BETWEEN may not work correctly. The essence of the question is why a query that displays data created on any date before 2021-04-14 and on the date 2021-04-14 itself displays fewer suitable rows than a query that displays rows created specifically on the date 2021-04-14. Let me describe it more simply:

AND DATE_FORMAT(`info`.`created`, '%Y-%d-%m') <= '2021-04-14' -- выводит 10 строк где `info`.`created` = 2021-04-14

AND DATE_FORMAT(`info`.`created`, '%Y-%d-%m') = '2021-04-14' -- выводит 13 строк где `info`.`created` = 2021-04-14


How can this be?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
C
cicatrix, 2021-04-15
@cicatrix

Here is the link:
https://www.techonthenet.com/mysql/between.php
Where it says
When using the MySQL BETWEEN Condition with dates, be sure to use the CAST function to explicitly convert the values ​​to dates.
I can't say exactly what is the difference between
STR_TO_DATE('2021-04-14 23:59:59', '%Y-%m-%d %H:%i:%s')
and
CAST('2014-02 -01' AS DATE)
but maybe it is.
In addition, perhaps it is not the date that plays the role, but the time.
2021-04-14 < 2021-04-14 11:46:23

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question