CODEWITHSUNDEEP
mysqlsqlselect
user3465736
user3465736

Reputation: 51

mysql statement (SELECT) based on date now

I would like to ask if my Mysql statement is correct or not.. When I run this under mysql it does not return any error but I cannot retrieve the row for it. Here's my statement:

SELECT * FROM timekeeping WHERE createddate = NOW()

Here's what my table looks like

enter image description here

Upvotes: 0

Views: 81

Answers (5)

armadadrive
armadadrive

Reputation: 981

My guess is that this isn't exactly what you want: NOW() function will return the exact timestamp for when the query is run, which means you are asking it for any records created at that exact moment in time.

You may want to try something more like:

SELECT * FROM timekeeping WHERE createddate = YOUR_DATE_CRITERIA

Upvotes: 0

Marc B
Marc B

Reputation: 360872

now() includes the time. Given that your fields contain date AND time values, you'll only ever get a match if the date AND time are exact matches. You need to compare dates only:

... WHERE DATE(createddate) = CUR_DATE()
               '2014-05-02' = '2014-05-02'

v.s.

... WHERE createddate = now()
'2014-05-02 22:14:00' = '2014-05-02 01:02:03'

Upvotes: 0

Alex
Alex

Reputation: 11579

Probably you want to search for today date. Try this:

SELECT * FROM timekeeping WHERE DATE(createddate) = DATE(NOW());

Upvotes: 1

Joseph B
Joseph B

Reputation: 5679

MySQL compare now() (only date, not time) with a datetime field

Try this:

SELECT * FROM timekeeping WHERE DATE(createddate) = DATE(NOW());

Upvotes: 3

skimon
skimon

Reputation: 1189

Most likely the createddate = NOW() is an exact time comparison , you are probably only interested in the year, month, day being the the same.

See here for details on how to do what you are trying to do:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 1

Related Questions