CODEWITHSUNDEEP
mysqldate
ChrVik
ChrVik

Reputation: 315

Is it possible to change format of date values in a MySQL column?

The database in use today was made 5 years ago and the entries in some of the date fields/columns are in a european string format, diplaying 29.05.2013 instead of 2013-05-29, the last one somewhat of a standard.

Regret the setup today, but it was done this way years ago due to lack of knowledge on the subject.

Is it possible to rewrite these column values? Or as a second solution, perhaps taking data from the old column and writing them to a new column while doing the transition?

Every row has a unique ID - testid

Database looks like this:

    testid        date1        date2        date3
    --------------------------------------------------
    001           2012-01-01   02.01.2012   04.01.2012
    002           2012-03-03   05.03.2012   10.03.2012

In date1 we're using timestamp, so it works fine. Would really like to have the other columns in the same format.

Hoping that someone with better know-how than myself have some suggestions.

Upvotes: 1

Views: 105

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You can update the columns and use string functions:

update t
    set date2 = concat_ws('-', left(date2, 4), right(date2, 2), substr(date2, 4, 2)),
        date3 = concat_ws('-', left(date3, 4), right(date3, 2), substr(date3, 4, 2));

After you do this, you can even alter the types of the columns:

alter table modify date2 date;
alter table modify date3 date;

EDIT:

The actual format is dd.mm.yyyy rather than mm.dd.yyyy, so:

update t
    set date2 = concat_ws('-', left(date2, 4), substr(date2, 4, 2), right(date2, 2)),
        date3 = concat_ws('-', left(date3, 4), substr(date3, 4, 2), right(date3, 2));

Upvotes: 3

e.dan
e.dan

Reputation: 7487

update t set date2=str_to_date(date2, '%d.%m.%Y'), date3=str_to_date(date3,'%d.%m.%Y');
alter table t modify date2 date;
alter table t modify date3 date;

Upvotes: 2

PerlDuck
PerlDuck

Reputation: 5730

Have a look here.

So first I'd suggest to convert the strings into a proper format (but keeping them as strings) like so:

update mytable set date2 = cast(str_to_date(date2, '%d.%m.%Y') as char(10)), 
                   date3 = cast(str_to_date(date3, '%d.%m.%Y') as char(10));

I'm not really sure whether the cast(... as char(10)) is really neccessary. Then, in a second step change the column's datatypes:

alter table mytable modify date2 date;
alter table mytable modify date3 date;

Upvotes: 0

Related Questions