Reputation: 1716

Having trouble querying the date in MySQL

I'm trying to select all interactions a user has done on a specific date with this query:

SELECT * FROM tblInteractions WHERE date(ctime) = 2016-09-30 AND userID = 8

But it fails, it brings back 0 results. I'm confused by this because this query:

SELECT date(ctime) FROM tblInteractions WHERE userID = 8

Returns:

date(`ctime`)   
2016-09-28     
2016-09-28
2016-09-28
2016-09-28
2016-09-28
2016-09-30
2016-09-30

What's wrong with my query? Is it a DB issue?

Upvotes: 0

Answers (3)

Reputation: 15061

Single quotes should resolve this:

SELECT * 
FROM tblInteractions 
WHERE DATE(ctime) = '2016-09-30' 
AND userID = 8

Upvotes: 2

Reputation: 4160

If ctime db column is a date type, try this:

SELECT * FROM tblInteractions
WHERE date(ctime) = STR_TO_DATE('2016-09-30', '%Y-%m-%d') AND userID = 8

Upvotes: 2

Reputation: 8545

 SELECT * FROM tblInteractions WHERE date(ctime) = '2016-09-30' AND userID = 8

you are missing single quotes for date value

Upvotes: 3