Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation using now() in query string, not returning expected results?

    Hi,

    i have the following query
    Code:
    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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    NOW() gives the current date *AND TIME*.

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

    Use CURDATE() instead of NOW().

  • Users who have thanked Old Pedant for this post:

    LJackson (08-09-2011)

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    brill thanks mate!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •