How do I convert german dates to MySQL standard dates?
date, mysql
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! :)
Problem
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?