MySQL DATE_FORMAT comparison to CURDATE() query...

Question

Hey guys, I am just trying to pull all the records from my database who have a rec_date (varchar) stored as m/d/Y and are expired (as in, less than curdate()), and this call isn't giving me what I want:

SELECT member_id, 
       status, 
       DATE_FORMAT(STR_TO_DATE(rec_date, '%m/%d/%Y'), '%Y-%m-%d') AS rec
  FROM members 
 WHERE rec_date > CURDATE() 
   AND status = '1'

I'm obviously doing something wrong, so can you help?

Answer 1

The right way - is to store data in proper field type. Use "date" type instead of "varchar".

anyway - you can use DATE_FORMAT to format rec_date to string right in WHERE clause and compare it to CURDATE().

Answer 2

I agree with zerkms that appropriate data types should be used whenever possible. Otherwise, you have to use a subquery to convert the string/varchar into a DATE/TIME data type first:

SELECT x.member_id,
       x.status,
       DATE_FORMAT(x.rec_dt, '%Y-%m-%d')
  FROM (SELECT m.member_id,
               m.status,
               STR_TO_DATE(m.rec_date, '%m/%d/%Y') AS rec_dt
          FROM MEMBERS m
         WHERE m.status = '1') x
 WHERE x.rec_dt < CURDATE()