I met with a dilemma. In some situations, the operator BETWEEN include a second date to, but some do not. Why it happens? For example I need to select the data for a specific date:

... AND ( `date` BETWEEN '2020-01-28' AND '2020-01-29' ) // In this case, the sampling is performed on two dates of 28 and 29
... AND ( `created_at` BETWEEN '2020-01-28' AND '2020-01-29') // In this case, the sampling takes place only on the date of 28
... AND (DATE ( `created_at`) BETWEEN '2020-01-28' AND '2020-01-29') // In this case, the sample is also taking place on two dates of 28 and 29

(field date in the database in DATE format, created_at in DATETIME)

How does it work?

Answer 1, Authority 100%

Any comparison operator BETWEEN and not the exception, leads all the operands of the same data type. In most cases this type – a type of the first operand in the text (and then wait type mismatch), or the most “common” of all (theoretically possible convertion failed), ie able to take all the operands.

As a rule, the choice is correlated with severity data typing system. MySQL / MariaDB liberal in this respect, so that there is a reduction to the most common types of.

Operator (x BETWEEN a AND b) is more compact recording aggregate ((x & gt; = a) AND (x & lt; = b))

In the first case, ... AND ( `date` BETWEEN '2020-01-28' AND '2020-01-29') , all three operands are of type date. Therefore selected and 28, and 29 numbers.

In the second case, ... AND ( `created_at` BETWEEN '2020-01-28' AND '2020-01-29') , we have 2 types of data, and a date-date time. More common is the date-time, and all operands are of this type. Missing components are zero values, and the operator is equivalent to ... AND ( `created_at` BETWEEN '2020-01-28 00:00:00' AND '2020-01-29 00:00:00') . Those. 28 will be set to any value, but for the 29th only the midnight, '2020-01-29 00:00:00') , and any value checking will not work with a non-zero component of the time.

In the third case, ... AND (DATE ( `created_at`) BETWEEN '2020-01-28' AND '2020-01-29') , the two operand data, the third – the function returning a date, so that all three operands are of type date, and sampled again get all the records, and 28, and 29, because the function simply resets the time component. That’s were it is rounded off – would be chosen only until noon, but what is not – that is not …

Answer 2

Because you have the field created_at DATETIME type, he is looking for up to 29 the number of 00:00:00 so you only see the number 28. If you want to fell 29 then put up to 30 numbers or specify the time