sql – Compare dates in MySQL

You can try below query,

select * from players
where 
    us_reg_date between 2000-07-05
and
    DATE_ADD(2011-11-10,INTERVAL 1 DAY)

That is SQL Server syntax for converting a date to a string. In MySQL you can use the DATE function to extract the date from a datetime:

SELECT *
FROM players
WHERE DATE(us_reg_date) BETWEEN 2000-07-05 AND 2011-11-10

But if you want to take advantage of an index on the column us_reg_date you might want to try this instead:

SELECT *
FROM players
WHERE us_reg_date >= 2000-07-05
  AND us_reg_date < 2011-11-10 + interval 1 day

This works for me:

select date_format(date(starttime),%Y-%m-%d) from data
where date(starttime) >= date 2012-11-02;

Note the format string %Y-%m-%d and the format of the input date.