MySQL load dates in mm/dd/yyyy format

Question

I've got a MySQL load script that almost works, it is perfect except for the date columns, which are not in a MySql friendly format.

load data infile  '/Users/pfarrell/sandbox/waybase/folklore/Titles_1976.csv'
into table fix76
fields terminated by ','
enclosed by '"'
ignore 1 lines
(  patentId,  USPatentNum,  title,  grantDate,  filedDate)

The problem is that my dates are in mm/dd/yyyy format. Looks like the str_to_date function is what I want, but I can't figure out how to use it in the load command.
I'm envisioning something like:

  grantDate = STR_TO_DATE(something, '%m/%d/%Y'),

but that doesn't work.

I have solved this situation by importing dates in a helper string column, and afterwords updating the table adding the dates with the conversion formula. This is not very nice to see, but works. Let's see if somebody has a better way... — perissf, Feb 13 '12 at 18:05
Did you try changing the locale for your connection to see if that would work? — Rowland Shaw, Feb 13 '12 at 18:08

score 10 · Accepted Answer · edited Mar 31 '14 at 19:50

You can load the date strings into user-defined variables, and then use STR_TO_DATE(@date, '%m/%d/%Y') to convert them to MySQL dates.

Try this:

load data infile  '/Users/pfarrell/sandbox/waybase/folklore/Titles_1976.csv'
into table fix76
fields terminated by ','
enclosed by '"'
ignore 1 lines
(  patentId,  USPatentNum,  title,  @grantDate,  @filedDate)
set grantDate = STR_TO_DATE(@grantDate, '%m/%d/%Y'),
filedDate = STR_TO_DATE(@filedDate, '%m/%d/%Y')

MySQL load dates in mm/dd/yyyy format

1 Answers1

Linked

Related