Compare field against date

Discussion in 'MySQL' started by lektrikpuke, Jun 23, 2009.

0
  1. #1
    I have some code that's supposed to compare the date in a MySQL field against today's date, and it just doesn't work.

    Help. :D

    The field (varchar) has a date like 6/29/2009 in it, and assuming today is 6/23/2009 why doesn't this work properly:

    UPDATE `tbl_name`
    SET `active` = '1'
    WHERE `field` >= 'date("m/d/Y")'
     
    lektrikpuke, Jun 23, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    You need to convert the varchar field to a valid date before it can be compared. Try something like this...

    UPDATE `tbl_name`
    SET `active` = '1'
    WHERE STR_TO_DATE(`field`, '%m/%d/%Y') >= NOW()

    Not sure what date you're trying to compare, so I used now. Also STR_TO_DATE in this case will put the date into a mysql date format (YYYY-MM-DD), not the same as the varchar field.

    Probably the best way to test is to do selects until you get what you are looking for. Then put it into an update.

    SELECT * FROM `tbl_name` WHERE STR_TO_DATE(`field`, '%m/%d/%Y') >= NOW()
     
    jestep, Jun 24, 2009 IP
  3. lektrikpuke

    lektrikpuke Well-Known Member

    Messages:
    297
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    113
    #3
    That does the trick. Thank you very much. :D
     
    lektrikpuke, Jun 24, 2009 IP