Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Compare dates in MySQL

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?

like image 481
NVG Avatar asked Sep 06 '10 13:09

NVG


People also ask

Can you compare dates in MySQL?

MySQL has the ability to compare two different dates written as a string expression. When you need to compare dates between a date column and an arbitrary date, you can use the DATE() function to extract the date part from your column and compare it with a string that represents your desired date.

How can I compare two dates in SQL?

Here we will see, SQL Query to compare two dates. This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.


1 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) 
like image 67
Nik Avatar answered Oct 15 '22 21:10

Nik