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

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 >= '2010-04-29' AND startTime < ('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  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

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

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

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

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

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

Examples related to mysql

Examples related to datetime

Examples related to comparison