php date question

Discussion in 'PHP' started by smudger, Dec 22, 2008.

0
  1. #1
    hi

    i have a large MySQL database (3000 records)- and the date fields are in the format YYYY-MM-DD, how can i change the to format to DD-MM-YYYY ?

    i don't want to have to change all 3000 records manually to the new format!
     
    smudger, Dec 22, 2008 IP
  2. crivion

    crivion Notable Member

    Messages:
    1,669
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    210
    Digital Goods:
    3
    #2
    I would do a php query like this
    <?php
    $sql = "select datefield from tablename";
    $rs = mysql_query($sql);
    while($row=@mysql_fetch_object($rs)) {
    $date = strtotime($row->timerow);
    $newdate = date("d-m-Y");
    $qry = "update tablename set datefield = '$newdate' where id = '$row->id'";
    $r = mysql_query($qry);
    }
    ?>
    cheers!
     
    crivion, Dec 22, 2008 IP
  3. phper

    phper Active Member

    Messages:
    247
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #3
    I don't know why you want to do that but running this one SQL query should fix it:
    UPDATE tableName SET dateField = CONCAT_WS('-', SUBSTRING(dateField, -2), SUBSTRING(dateField, 5, 2), SUBSTRING(dateField, 0, 4));
    Code (markup):
     
    phper, Dec 22, 2008 IP