0

I have a csv file I'm trying to parse a bunch of strings and a few are date formats in my database. The code looks a little like this:

load data infile '/temp/db.csv'
into table db.dbtable
fields terminated by '|'
lines terminated by '\n'
ignore 1 rows

(db_id,
dbtable_field1,
db_date,
db_date2)

set
db_date = convert(char(10), Datetime),
db_date2 = convert(char(10), Datetime);

Every time I execute this it comes up with this error:

Error Code: 1292. Incorrect datetime value: '<date time>' for column 'db_date' at row 1

I've been using stackoverflow and other means to try to get this to work, but nothing seems to be working. Am I missing something important here?

--EDIT--

The csv file looks a little something like this:

db_id|dbtable_field1|db_date|db_date2
123|0025AAA|8/12/2014 10:24:32 AM| 8/12/2014 10:24:32 AM
124|0096AB54|2/26/2013|2/26/2013
125|0085ABDE|10/2/2014 4:56:00 PM|10/2/2014 4:56:00 PM

This block isn't exactly my csv file but it's the same format. As you can see the date format changes and thus I cannot use a predefined date format. Currently I am just parsing it as a varchar(25) in order to get it into the database simply, but parsing it into the date for further work would be useful later on (ie. date comparison testing).

I have done this in mutiple ways including:

db_date = str_to_date(@db_date, '%c/%d/%Y %h:%i:%s %p');

and

db_date = convert(char(10), getDate(), 125);

And neither have worked due to the inconsistency in date format.

JFrench
  • 3
  • 2
  • 1
    Can you show some lines from your csv file? – Jens Sep 09 '14 at 13:36
  • I tink you should use STR_TO_DATE function, http://stackoverflow.com/questions/1908394/mysql-using-a-string-column-with-date-text-as-a-date-field – catalinetu Sep 09 '14 at 13:44
  • Yes... show us what you are trying to parse. An initial advice I have for you is to first load the data in char (or varchar) columns, and then calculate the dates with an update, or an insert into another table. Splitting the process in two steps helps in better analyzing the input data and in testing the conversion SQL. – Frazz Sep 09 '14 at 13:44
  • Sorry, pretty new to posting questions on StackOverflow, my current fix is Frazz's answer, but it would be easier just to have it done as simple and easy as possible. I updated it to further explain my issue that I am having with an example of basically what everything looks like. – JFrench Sep 09 '14 at 14:55

0 Answers0