Compare dates in MySQL

Question

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   gt    2000-07-05  AND CONVERT CHAR 10  us reg date 120   lt    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   lt      2011-  at line 1   How can this problem be fixed

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This works for me   select date format date starttime    Y- m- d   from data where date starttime   gt   date  2012-11-02     Note the format string   Y- m- d  and the format of the input date

[mysql] Compare dates in MySQL

Examples related to mysql

Examples related to sql

Examples related to comparison