convert date into mysql datetime format-CodePudding

I have datetime in the following format in a csv data

In_Time
1/1/17 1:07 AM
1/1/17 12:59 PM

I am unable to load data with a column that holds values like above. SO I loaded the data in text format for the In_Time column and I am trying to use STR_TO_DATE() function to now convert the column into a datetime column in mysql.

I am trying the below code but it gives me error:

Incorrect datetime value: '1/1/17 12:27 AM' for function str_to_date

UPDATE mytable
SET In_Time = STR_TO_DATE(ED_Arrival_Time, '%d/%m%y %h: %i: %p');

Please help.

CodePudding user response：

As a general principle, it's good to break the problem down into the smallest part that is causing a problem and solve that. In this case, I think that is the format specifier for the STR_TO_DATE() function.

Find a list of format specifiers: https://www.w3schools.com/sql/func_mysql_str_to_date.asp

Open a MySQL terminal, then iteratively try it with a few of your strings until you get the correct format specifier string which should be something like this:

select str_to_date('1/1/17 1:07 AM', '%e/%c/%y %I:%i %p') as test_date_parse;

Then adjust your code with the correct date specifier.

CodePudding user response：

There is no such time as 12:59 PM however given the correct formatting options for str_to_date https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format for the dates passed the result will be null and no error is thrown

DROP TABLE IF EXISTS T;

create table t
( ED_Arrival_Time varchar(20), In_Time datetime);

insert into t values
('1/1/17 1:07 AM',null),('1/1/17 11:59 PM',null),('1/1/17 23:59 PM',null);

select ED_Arrival_Time, str_to_date(ed_arrival_time,'%d/%m/%y %h:%i %p')
from t;

 ----------------- -------------------------------------------------- 
| ED_Arrival_Time | str_to_date(ed_arrival_time,'%d/%m/%y %h:%i %p') |
 ----------------- -------------------------------------------------- 
| 1/1/17 1:07 AM  | 2017-01-01 01:07:00                              |
| 1/1/17 11:59 PM | 2017-01-01 23:59:00                              |
| 1/1/17 23:59 PM | NULL                                             |
 ----------------- -------------------------------------------------- 
3 rows in set, 1 warning (0.001 sec)

The expectation is that all the incoming dates are in the same format - if not then you need to cleanse them.

BTW load data infile can manipulate data loading from a csv file see the section Input Preprocessing in the manual https://dev.mysql.com/doc/refman/8.0/en/load-data.html