0 votes
1 view
in SQL by (6.1k points)
edited by

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? 

1 Answer

0 votes
by (12.7k points)

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 :

Related questions

0 votes
1 answer
asked Jul 15, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Aug 10, 2019 in Java by Suresh (3.4k points)
0 votes
1 answer
0 votes
1 answer
asked Dec 29, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...