update mysql char data type to date with two format data

Question

i have a column with 'char' data type with two format records like this,

my_char_column
2030-05-12
2/8/2017
2012-12-13

the problem i want to change type data to 'date' with 'Y-m-d' format like this,

my_char_column_new
2030-05-12
2017-08-02
2012-12-13

i have tried to use this query but doesn't work

UPDATE my_table SET my_char_column = DATE_FORMAT(my_char_column,'%Y-%m-%d') where my_char_column LIKE'%/%'

Does all date only have these two formats?

Blank
– Blank

2017-11-24 07:43:46 +00:00
Commented Nov 24, 2017 at 7:43 — Blank
– Blank, Commented Nov 24, 2017 at 7:43
yes, just two formats

wahmal
– wahmal

2017-11-24 11:39:29 +00:00
Commented Nov 24, 2017 at 11:39 — wahmal
– wahmal, Commented Nov 24, 2017 at 11:39

Shivangi Gupta · Accepted Answer · 2017-11-24 08:44:33Z

2

This is a similar answer: https://stackoverflow.com/a/12734990/8364246

Here is my way: What you need here is some kind of condition check that can choose between two possibilities, IFNULL will serve that purpose. DATE_FORMAT(STR_TO_DATE(my_char_column, '%Y-%m-%d') this convert "2030-05-12" this type of date, while DATE_FORMAT(STR_TO_DATE(my_char_column, '%d/%m/%Y') will convert "2/8/2017" this type of date to the format you want i.e. %Y-%m-%d.

select IFNULL(DATE_FORMAT(STR_TO_DATE(my_char_column, '%Y-%m-%d'), '%Y-%m-%d'), DATE_FORMAT(STR_TO_DATE(my_char_column, '%d/%m/%Y'), '%Y-%m-%d')) from your_table;

edited Nov 24, 2017 at 8:44

answered Nov 24, 2017 at 7:54

Shivangi Gupta

311 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wahmal Over a year ago

thanks for your answer, but that's not change my format date. are you have idea to select only like '2/8/2017' format ? i will change to 'y-m-d' format first before change the type data.

Shivangi Gupta Over a year ago

You can use a script in php or python to read columns one by one, then split the columns with '/', if the size of array thus created is more than one, the means the date is of "2/8/2017" format. Then you can combine it back to "2017-8-2" format. Then all the columns will be converted to same format. Then you can directly use STR_TO_DATE() function to convert string to date, or you can use your script only to convert string to date and insert back to table.

wahmal Over a year ago

yes, i was thinking about that. I just try to find more eficient way to do that because i have 27530 record to change it. Thnks for your time.

Collectives™ on Stack Overflow

update mysql char data type to date with two format data

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related