Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL load dates in mm/dd/yyyy format

Tags:

mysql

parsing

csv

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.

like image 449
fishtoprecords Avatar asked Feb 13 '12 17:02

fishtoprecords


People also ask

How do I change one date format in MySQL?

Use STR_TO_DATE() method from MySQL to convert. The syntax is as follows wherein we are using format specifiers. The format specifiers begin with %. SELECT STR_TO_DATE(yourDateColumnName,'%d.

How are dates formatted in MySQL?

MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts.

How do I change the date format in SQL Workbench?

You can set a custom date format for a specified column in a DataSet job in Workbench 4. You do this by adding the Custom Date Format transform to the DataSet job and then selecting the column and specifying the desired date format.


1 Answers

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')
like image 180
Ike Walker Avatar answered Nov 15 '22 18:11

Ike Walker