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?

Preprocessing your CSV with a regular expression find and replace would take about one minute to fix this. — Jon, Oct 30 '12 at 10:46
If you are interested in building an import tool then by all means do not use it. OTOH if you are interested in importing the data so that you can get on with real work perhaps you should consider it. ;-) — Jon, Oct 30 '12 at 12:15

dan · Answer 1 · 2012-10-30T11:05:42.433

4

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:

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

edited Oct 30 '12 at 11:05

answered Oct 30 '12 at 10:46

dan

13,132
3
38
49

Tried this, but how do you use this in conjunction with Sequel Pro? I.e. getting each date and then automatically entering it into the function? – Kebman Oct 30 '12 at 10:56

score 3 · Accepted Answer · answered Oct 30 '12 at 12:20

3

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

answered Oct 30 '12 at 12:20

Kebman

1,901
1
20
32

Sorry for the wild goose chase, but I found all answers myself. The wildcards/parameters in Sequel Pro are simply denoted $1, $2, $3, etc, thus it's a trivial matter of entering a wildcard into the automated forms with a STR_TO_DATE function in SP to get the wanted result. This way I don't even need my own SQL-only answer either for this operation, but I guess it's nice to have for reference. – Kebman Mar 17 '14 at 13:31
1

Kebman, I'm trying to do the same type import using a date with Sequel Pro. It's sounds like you've found the answer, but I don't understand. Where did you put this code? When did you perform this step in your workflow? Are you still doing the import with Sequel Pro import tool or is this something you do post import? – Tim Elhajj May 03 '15 at 14:59
I do it when I prepare to import a CSV file. There is an option in the program to import CSV files. Just make the necessary edits, or put the code block in the code editor and execute it from there. – Kebman May 03 '15 at 15:03
Okay, I have been using the import function in SP. I get the same error as above. So what are the necessary edits? Do I add this code in the window where I map the CSV fileds to the fields in my table – Tim Elhajj May 03 '15 at 15:08
I opened a new question very similar to this one, but I haven't found the answer yet. http://stackoverflow.com/questions/30018355/how-to-change-a-string-to-a-date-during-import-using-sequel-pro – Tim Elhajj May 05 '15 at 01:35

score 1 · Answer 3 · answered Oct 30 '12 at 10:50

It is easier if your CSV import would contain a date as a MySQL string, but it can be done otherwise too:

Step 1: Define a varchar(10) for your german dates and import the data.

Step 2: Add another field to your table:

ALTER TABLE `yourtable` 
ADD COLUMN `your_mysql_date` DATE NULL;

Step 3: Move the data:

UPDATE yourtable
SET your_mysql_date = CONCAT(
    RIGHT(your_german_date,4),
    '-',
    MID(your_german_date,4,2),
    '-',
    LEFT(your_german_date,2)
);

...done!

There might be an easier way to solve this, but this way you have alot of control over the data and the formatting.

How do I convert german dates to MySQL standard dates?

3 Answers3