気になったこと、勉強したこと、その他雑記など色々メモしていきます。。Sometimes these posts will be written in English.,

Behavior of "BETWEEN" operator for datetime in MySQL.

I had a big misunderstanding to the "BETWEEN" operator respect to datetime columns in MySQL.

I had thought that "BETWEEN" does not contain the value of end when the date value is used respect to datetime columns like this.

SELECT * FROM sample WHERE some_datetime_col BETWEEN "2017-01-01" AND "2017-02-01"

In Above case, I had thought that the records that has "2017-02-01 xx:xx:xx" value in its some_datetime_col wouldn't be fetched.

But it was misunderstanding.
Actually in this case, the record that has "2017-02-01 00:00:00" is fetched.

The truth, "BETWEEN" definitely contains the start and end values. And also if the date values are as used respect to datetime columns, its time part is filled of "00:00:00". So that about continuous datetime records like "2017-02-01 00:00:00", "2017-02-01 00:00:01", ...,"2017-02-01 23:59:59" to be considered, only "2017-02-01 00:00:00" record will be unexpectedly fetched in these range.

The Point of this post is that when using date values to datetime columns, date is forcely casted to datetime with appending 00:00:00.