What's wrong with this SQL statement?:

SELECT * FROM vbsPosts WHERE createdBy='Dave' AND createdOn = CURDATE()

I want to select records that have field createdBy equal to Dave and field createdOn equal to today's date. 😕

    Which DBMS? Most of them have different functions for getting current date.

      I don't get any error, it just reports 0 when I run this code:

      $upt = mysql_num_rows(mysql_query("SELECT * FROM vbsPosts WHERE createdBy='Dave' AND createdOn = CURDATE()"));

      It should return 3 records, but it's reporting 0. So I'm thinking I'm using CURDATE() wrong?

      BTW, createdOn is a datetime field.

        Nothing if createdOn is a date.

        If it is a datetime value, you need to compare JUST the date. Use substring or date_format.

          Originally posted by ultraslacker
          Nothing if createdOn is a date.

          If it is a datetime value, you need to compare JUST the date. Use substring or date_format.

          Good call. I'll give that a try. Thanks alot!

            SELECT * FROM vbsPosts WHERE createdBy='Dave' AND DATE_FORMAT(createdOn, '%Y-%m-%d') = CURDATE()

            Works fine when I run it in phpMyAdmin, but when I try to use it in a script with mysql_num_rows, it gives me 0?

              Nevermind. I got it working great. Thanks for your help. 🙂

                Write a Reply...