MySQL How to select date from a datetime type field

Question

There is a field created_at is of datetime type. How to select data on a specific date?

SELECT * FROM data WHERE created_at = '2017-06-29';

SELECT * FROM data WHERE created_at BETWEEN '2017-06-29 00:00:00' AND '2017-06-29 23:59:59';

None of them are correct, the result sets are all empty

Answer 1

Reference article: MySQL time processing related functions

WHERE DATE(created_at) = '2017-06-29'

or

WHERE created_at LIKE '2017-06-29%'

Answer 2

The default format of datetime is '2017-06-29 00:00:00'. If your data does have data on this day, the second item will have results, so first you have to check whether there is data on this day in the table. And what is your data format?

PS: To get the data of a certain day, you can create_at like 'yyyy-mm-dd%' or your second way of writing, date(created_at) and left(created_at) can also get the date of the day, but they will not be indexed , the efficiency is very poor

Answer 3

I can, but maybe your table or field is wrong, or there is indeed no result. . The conditions are no problem

Answer 4

select * from data d WHERE d.created_at >= curdate()
and d.created_at < DATE_ADD(STR_TO_DATE(curdate(), '%Y-%m-%d'), INTERVAL 1 DAY);

It means to query the time that is greater than or equal to 0:00 today and less than 0:00 tomorrow

Answer 5

The first line is wrong because the datetime format is different from the format in your condition.

The second line is correct.
The result is also correct, because there is indeed no data.