Compare dates in MySQL

Question

I want to compare a date from a database that is between two given dates. It is the column from the database is DATETIME, and I want it 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 am getting 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? 

Answer 1

That is the SQL Server syntax for converting a date to a string. In the 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 would like 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

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.

For more information visit :