...

View Full Version : using now() in query string, not returning expected results?



LJackson
08-08-2011, 11:59 PM
Hi,

i have the following query

SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding, filmReleaseDate, COUNT(*) AS Acount
FROM tbl_dvds AS dvd LEFT JOIN tbl_popularity AS pop
ON dvd.filmID = pop.prodID
WHERE dvd.filmReleaseDate = NOW()
GROUP BY filmID, filmName, filmIMG, filmRRP, filmBinding
ORDER BY Acount DESC
LIMIT 10

the filmReleaseDate field is in a date format,

and i have at least 1 record with a release date of 2011-08-08 but the above query doesnt find it, what have i done wrong?

thanks

Old Pedant
08-09-2011, 12:18 AM
NOW() gives the current date *AND TIME*.

SO it will *ONLY* match a date-only field at *exactly* midnight.

Use CURDATE() instead of NOW().

LJackson
08-09-2011, 12:22 AM
brill thanks mate!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum