How to change column type from text to date in mysql

Question

I uploaded a csv file to a mysql database. Inside the table there is a time column, which has as the name already says time information :), please have a look This column is saved as text

I try to convert time column in datetime, and found following help on stackoverflow MySQL alter table and convert data from text to datetime

So I tried to go the same way, i.e.

 alter table 'tablename' add column new_column_name date;

 update 'tablename'
 set new_column_name=str_to_date(column_with_time_saved_as_text,'YYYY-MM-DD hh:mm:ss')

If I execute the script I get following error Error Code: 1411. Incorrect datetime value: '2019-07-03 23:47:46.254+02' for function str_to_date

I suppose there is an issue with the timezone.I tried to find out a solution but I could not. Thanks for any kind of advice

Why not create a new table with correct datatype, and insert the data from source to new one and do some fiddling remving the + all data after using substring_index? — Mad Dog Tannen, Oct 17 '19 at 13:09
You may find this useful for your case https://stackoverflow.com/questions/14961490/mysql-error-code-1411-incorrect-datetime-value-for-function-str-to-date — Vipul Patil, Oct 17 '19 at 13:10
@Kay. I dont understand your approach. I get a csv file and upload it to mysql DB as a new table. Even if I create a new table I will have the same issue to convert the column type from text to date :) — SMS, Oct 17 '19 at 13:20

user2342558 · Accepted Answer · 2019-10-17T13:58:13.007

1

This error:

Error Code: 1411. Incorrect datetime value: '2019-07-03 23:47:46.254+02' for function str_to_date

means that you are using a wrong formatting parameter, as explained here you must use formatters in the form %dateField like this:

 SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");

Alternatively, you can use SUBSTRING to get only the datetime part you need:

alter table 'tablename' add column new_column_name datetime;
update 'tablename' set new_column_name = SUBSTRING(column_with_time_saved_as_text, 1, 19)

edited Oct 17 '19 at 13:58

answered Oct 17 '19 at 13:18

user2342558

5,567
5
33
54

if I execute your code I only the year information, i.e. 2019-07-03 – SMS Oct 17 '19 at 13:56
@SMS because the new filed was setted as DATE instead of DATETIME. see my edit (`add column new_column_name datetime;`) – user2342558 Oct 17 '19 at 13:58
ah perfect. than you very very much :) – SMS Oct 17 '19 at 14:01

Sathish G · Answer 2 · 2019-10-18T06:50:29.223

1

You can perform str_to_date

    SELECT DATE_FORMAT(STR_TO_DATE('2019-11-03 23:59:46.254+02', '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s') dt;

         dt
   2019-11-03 23:59:46

Using '%Y-%m-%d %H:%i:%s' update the table

    alter table 'tablename' add column new_column_name datetime;

    update 'tablename'
    set new_column_name= DATE_FORMAT(STR_TO_DATE(column_with_time_saved_as_text,19), '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s');

But this will ignore the '254+02'.

edited Oct 18 '19 at 06:50

answered Oct 17 '19 at 13:32

Sathish G

138
6

I also used this code but I also get the same message "Error Code: 1292. Truncated incorrect datetime value: '2019-07-03 23:47:46.254+02' " – SMS Oct 17 '19 at 13:52
Maybe try to take only datetime part : update tablename set new_column_name = DATE_FORMAT(STR_TO_DATE(left(column_with_time_saved_as_text,19), '%Y-%m-%d %H:%i:%s %x'), '%Y-%m-%d %H:%i:%s %x'); – Sathish G Oct 18 '19 at 06:47

How to change column type from text to date in mysql

2 Answers2