How do I convert german dates to MySQL standard dates?

Question

I'm importing a CSV file with dotted german dates into a MySQL database. I want the dates in the CSV to automatically be formatted correctly to the correct data type fields used by MySQL.

I'm using Sequel Pro for the import. I gather I'm supposed to use the STR_TO_DATE function, but I just can't wrap my head around how to use Add Value or Expression in the program.

German date

Here are the dates in the CSV file:

DD.MM.YYYY e.g.: 28.01.1978

MySQL date

Here is what I want to end up with in the database:

YYYY-MM-DD
e.g.: 1978-01-28

Here's what I've tried

I put in STR_TO_DATE('$5', '%d.%m.%Y'); into Add Value or Expression, but this only gives the following error message:

[ERROR in row 1] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '06.04.1997'', '%d.%m. %Y');,'2KMX','43354997')' at line 2

Any ideas?

Answer 1

You need import the date field in a varchar fields (temp_varchar_field) first, after that, you can use something like:

update table_name set final_date=STR_TO_DATE(temp_varchar_field,'%d.%m.%Y');

You should do something like:

1. Create a temporary field: alter table table_name add column temp_varchar_field varchar(10);
2. Import, using Sequel Pro, the CVS file but using the temp_varchar_field for the date.
3. update table_name set final_date=STR_TO_DATE(temp_varchar_field,'%d.%m.%Y');
4. Delete the temp field if everything was imported properly. Using: alter table_name drop column temp_varchar_field;

Answer 2

I just got it to work with this piece of SQL-code:

load data local infile 'myfile.csv' into table `mytable` 
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(surname, name, @germandate, telephone, etc)
set birthyear = STR_TO_DATE(@germandate , "%d.%m.%Y")
;

The clue here being the @germandate variable which is turned into the default MySQL date by setting the respective column with STR_TO_DATE(). No hacks needed! :)