STR_TO_TIME convert

Question

have a string field in DB which I need to convert to datetime, my problem is I need to convert date format from 03/01/2019 11:30 to 2011-01-26 14:30:00. I can do it with an individual record, but can't figure out how to convert all the records in the table.

I followed the advice here

but, all converted dates are 0000-00-00 00:00:00 presumably because mysql not able to guess input date format.

create table wms_lw.wms_lw (
site_id int null, 
name varchar(255) null,
site varchar(255) null,
date varchar(255) null, 
new_date datetime not null, 
value double null, 
date_2 varchar(255) null 
);

create table wms_lw.wms_lw ( site_id int null, name varchar(255) null, site varchar(255) null, date varchar(255) null, new_date datetime not null, value double null, date_2 varchar(255) null ); — Henryd, Nov 22 '19 at 14:44
first off all, its not good to store date, timestamp as varchar in database, you can use timestamp or datetime type. — Zeljka, Nov 22 '19 at 14:46
`UPDATE wms_lw.wms_lw SET date = STR_TO_DATE(date,'%d-%M-%Y %h:%i')` you can use this query to change date format in all rows — Zeljka, Nov 22 '19 at 14:48
@Zeljka agree, not sure how to convert the date formats other than str_to_date — Henryd, Nov 22 '19 at 14:50
https://stackoverflow.com/questions/4706289/mysql-convert-varchar-to-date — Zeljka, Nov 22 '19 at 14:51
`UPDATE wms_lw.wms_lw SET date = date_format(str_to_date(date, '%d/%m/%Y %h:%i'), '%Y-%m-/%d %h:%i:%s'); ` — Zeljka, Nov 22 '19 at 14:59

score 0 · Answer 1 · answered Nov 22 '19 at 14:45

You can convert your string in date as

select str_to_date('03/01/2019 11:30', '%d/%m/%Y %i:%s')

so from your table

select str_to_date(my_col, '%d/%m/%Y %i:%s') from my_table

once you have a date you can format as you prefer wit date_format() or add the datte time using date_Add()

STR_TO_TIME convert

1 Answers1