CODEWITHSUNDEEP
mysqlsql
andyknas
andyknas

Reputation: 1957

Simple MySQL timestamp query fails

I've got a query that's been running as a daily job that's worked for years. Somehow, it stopped yesterday.

I have a timestamp field which has proper values and a query...

SELECT * 
FROM  `shoppingcart` 
WHERE  `creation_datetime` like  '2013-06-16'

I get back an empty resultset. If I add a % after the date it works.

MySQL is 5.1.61, nothing's been updated on the server.

Upvotes: 0

Views: 90

Answers (3)

juergen d
juergen d

Reputation: 204746

If you only want the date part of a datetime column you can use DATE() to extract it

SELECT * 
FROM shoppingcart
WHERE date(creation_datetime) = '2013-06-16'

But this won't use indexes as @eggyal mentioned. That is why you should better use 

SELECT * 
FROM shoppingcart
WHERE creation_datetime >= '2013-06-16' 
AND creation_datetime < '2013-06-17'

Upvotes: 5

Rob W
Rob W

Reputation: 9142

datetime types have dates and times. Always. If you omit the time portion, it's same as using all zero's, and thus would only match records where the time is exactly midnight. This is why % is working - because it accepts any time.

Example of datetime: YYYY-MM-DD HH:MM:SS

Upvotes: 3

Hanky Panky
Hanky Panky

Reputation: 46900

When you use LIKE, you have to use % signs, otherwise LIKE acts same as =. Try

LIKE  '2013-06-16%'

Otherwise you can simply use the DATE function

DATE(creation_datetime) = '2013-06-16'

Upvotes: 1

Related Questions