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.
2
Answers
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
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
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:
Then adjust your code with the correct date specifier.