当type为timestamp时，查找两个日期之间的记录不起作用

Question

I have one table which having dates and I want to find date between two dates.

For created_date table field, I have used timestamp type. Now I have created following sql query to find records between to dates like 2015-04-15 to 2015-04-21.

My Table

id  created_date
**  *******************
10  2015-04-15 10:49:26
13  2015-04-17 09:49:06
14  2015-04-21 14:59:42
15  2015-05-20 14:26:07
16  2015-06-11 13:25:34

My Select Query

SELECT *
FROM table
WHERE created_date
BETWEEN '2015-04-15'
AND '2015-04-21'
ORDER BY created_date DESC
LIMIT 0 , 10

As I am using timestamp type from above table, sql is not working becouse I am comparing with this data formate YYYY-MM-DD. I want to get records with using timestamp type and dont want to change from table but fetch results without reading HH:MM:SS.

For example, If I search with this two dates: 2015-04-15 to 2015-04-21

I want this records:

id  created_date
**  *******************
10  2015-04-15 10:49:26
13  2015-04-17 09:49:06
14  2015-04-21 14:59:42

Any Idea how to get records like this :)

Thanks.

Answer 1

You can use MySQl DATE().

SELECT *
FROM table
WHERE DATE(created_date)
BETWEEN '2015-04-15'
AND '2015-04-21'
ORDER BY created_date DESC
LIMIT 0 , 10