Data field update in string format

Asked

Viewed 256 times

1

Guys, I took an old bank from a client where there’s a birth date column that’s in a string format and doesn’t date. So far so good only that it has certain recorded dates that do not have the 0 in the month or day when less than 10. I need to include the zero where you do not have. Example: Birth= 3/3/1980 I need to change to 03/03/1980 as I do this in the update? Thanks

mysql sql-update

  • Do you want to adjust on demand? Or you can run some specific process once just to adjust across table?

  • @Murillogoulart I can run a process to adjust only once. One hundred so already sets the whole table and then their new system keeps the rest.

2 answers

1


If there is an id (it can be another unique field for each date)

$query = mysql_query("SELECT id,data FROM suatabela");

while($row = mysql_fetch_array($query))
{
    $id= $row['id'];
    $data= $row['data'];

    $partes = explode('/',$data);
    $dia=$partes[0];
    $mes=$partes[1];
    $ano=$partes[2];

    if (strlen($dia)==1){
        $update="true";
        $dia="0".$dia;
    }
    if (strlen($mes)==1){
        $update="true";
        $mes="0".$mes;
    }

    if ($update=="true"){
        $result=$dia."/".$mes."/".$ano;
        mysql_query("UPDATE suatabela SET data='$result' Where id='$id'"); 
    }
}

Note: mysql is discontinued

  • Friend thanks for the help, but I need the code in Mysql to run as a query.

-1

Makes the adjustment fixed, IE:

3/3/1980

If the second character is "/", add 0 at the first position;

03/3/1980

If the third character is "/" adds 0 in the third position;

03/03/1980

  • Your reply does not match his friend’s request.

  • @Douglas Pode exemplificar?

  • What I meant is that by his question he asks an example of what an update would look like (a real example). You gave him an idea of logic and it’s not what he asks, you understand ?

Browser other questions tagged mysql sql-update

You are not signed in. Login or sign up in order to post.