MySQL compare DATE string with string from DATETIME field

Question

I have a question  Is it possible to select from a MySQL database by comparing one DATE string  2010-04-29  against strings that are stored as DATETIME  2010-04-29 10 00    I have one date picker that filters data and I would like to query the table by the DATETIME field like this   SELECT   FROM  calendar  WHERE startTime    2010-04-29        and I would like to get the row that has the DATETIME value of  2010-04-29 10 00    Any suggestions  Thanks

User · Answer

You can cast the DATETIME field into DATE as   SELECT   FROM  calendar  WHERE CAST startTime AS DATE     2010-04-29    This is very much efficient

User · Answer

If you want to select all rows where the DATE part of a DATETIME column matches a certain literal  you cannot do it like so   WHERE startTime    2010-04-29    because MySQL cannot compare a DATE and a DATETIME directly  What MySQL does  it extends the given DATE literal with the time  00 00 00   So your condition becomes   WHERE startTime    2010-04-29 00 00 00    Certainly not what you want    The condition is a range and hence it should be given as range  There are several possibilities   WHERE startTime BETWEEN  2010-04-29 00 00 00  AND  2010-04-29 23 59 59  WHERE startTime  gt    2010-04-29  AND startTime  lt    2010-04-29    INTERVAL 1 DAY    There is a tiny possibility for the first to be wrong - when your DATETIME column uses subsecond resolution and there is an appointment at 23 59 59   epsilon  In general I suggest to use the second variant   Both variants can use an index on startTime which will become important when the table grows

User · Answer

SELECT   FROM sample table WHERE last visit   DATE FORMAT  2014-11-24 10 48 09    Y- m- d  H  i  s     this for datetime format in mysql using DATE FORMAT date format

User · Answer

SELECT   FROM  calendar  WHERE DATE startTime     2010-04-29     it helps   you can convert the values as DATE before comparing

User · Answer

SELECT   FROM  calendar  WHERE startTime like  2010-04-29     You can also use comparison operators on MySQL dates if you want to find something after or before  This is because they are written in such a way  largest value to smallest with leading zeros  that a simple string sort will sort them correctly

User · Answer

Use the following    SELECT   FROM  calendar  WHERE DATE startTime     2010-04-29    Just for reference I have a 2 million record table  I ran a similar query  Salils answer took 4 48 seconds  the above took 2 25 seconds   So if the table is BIG I would suggest this rather

User · Answer

SELECT   FROM  calendar  WHERE DATE FORMAT startTime    Y- m- d      2010-04-29     OR   SELECT   FROM  calendar  WHERE DATE startTime     2010-04-29

[mysql] MySQL compare DATE string with string from DATETIME field

Examples related to mysql

Examples related to datetime

Examples related to comparison