I want to compare a date from a database that is between 2 given dates. The column from the database is DATETIME, and I want to compare it only to the date format, not the datetime format.
SELECT * FROM `players` WHERE CONVERT(CHAR(10),us_reg_date,120) >= '2000-07-05' AND CONVERT(CHAR(10),us_reg_date,120) <= '2011-11-10'I get this error when I execute the SQL above:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'us_reg_date,120) >= '2000-07-05' AND CONVERT(CHAR(10),us_reg_date,120) <= '2011-' at line 1
How can this problem be fixed?
5 Answers
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 dayThis 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.
I got the answer.
Here is the code:
SELECT * FROM table
WHERE STR_TO_DATE(column, '%d/%m/%Y') BETWEEN STR_TO_DATE('29/01/15', '%d/%m/%Y') AND STR_TO_DATE('07/10/15', '%d/%m/%Y')this is what it worked for me:
select * from table
where column
BETWEEN STR_TO_DATE('29/01/15', '%d/%m/%Y') AND STR_TO_DATE('07/10/15', '%d/%m/%Y')Please, note that I had to change STR_TO_DATE(column, '%d/%m/%Y') from previous solutions, as it was taking ages to load
