[SOLVED] How do I convert german dates to MySQL standard dates?

How do I convert german dates to MySQL standard dates?

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?

Solution

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! :)