Compare dates in MySQL
This works:
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. For example 2012-11-02
instead of 12-11-2
.
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
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)