0
124
ViewsMySQL date search doesn't work expectedly on datetime field if time is also included in the query
I am wondering why the following query doesn't yield any result if time is also included in the query?
SELECT * FROM `tb_posts` WHERE DATE(`post_date_published`) = '2013-05-05 16:20:58'
However, when time is excluded from the search parameter
SELECT * FROM `tb_posts` WHERE DATE(`post_date_published`) = '2013-05-05'
Then it fetches the related record from database.
How can we fetch record still using time in the query?
1 answers
Answer question0
When you compare a DATE with an expression that isn't also DATE, it treats the date as being at time 00:00:00. This is mentioned in the documentation:
When you compare a DATE, TIME, DATETIME, or TIMESTAMP to a constant string with the <, <=, =, >=, >, or BETWEEN operators, MySQL normally converts the string to an internal long integer for faster comparison (and also for a bit more “relaxed” string checking).
So it's comparing the numeric representation of the date with the numeric representation of the datetime string constant, and they don't match because of the different times.
So you need to remove the time from the string:
SELECT * FROM `tb_posts` WHERE DATE(`post_date_published`) = DATE('2013-05-05 16:20:58')