loading date strings as dates

[yasaswi]

Now that MySQL 5.0.15 is production ready, are there any functions to
use with mysqlimport, so I can load a string such as, MM/dd/yy, in a
text file into a MySQL database as a yyyy-MM-dd date value.
Thanks,
Yasaswi

[Markus Popp]

I doubt that you can do this directly. I would import the date values into a
char field and use the substring function to build the date in the right
format that works with a date field.

Markus

[yasaswi]

Hi Markus:
What do you mean by import the date values into a char field? I have a
data comma separated text file. One column has date in MM/dd/yy format?
Using mysqlimport command, how would I do what you are suggesting?
May be I can run a perl or shell script on the file first, to re-format
all the data columns in yyyy-MM-dd format and then run the mysqlimport
command. Please comment.
Thanks,
Yasaswi
Markus Popp wrote:

I doubt that you can do this directly. I would import the date values into a
char field and use the substring function to build the date in the right
format that works with a date field.

Markus

[Bill Karwin]

yasaswi wrote:

Hi Markus:
What do you mean by import the date values into a char field? I have a
data comma separated text file. One column has date in MM/dd/yy format?
Using mysqlimport command, how would I do what you are suggesting?
Design the table into which you are importing the data so that the field
in the position matching your MM/dd/yy values is a CHAR(10) or similar.

After you import the data successfully, add a date column to the table
(using ALTER TABLE), and copy the values from the CHAR(10) field to the
date field, while parsing the values:

UPDATE myTable
SET dateField = STR_TO_DATE(cha rField, '%m/%d/%y');

Then drop the CHAR(10) field if you wish.

See docs on STR_TO_DATE() function here:
http://dev.mysql.com/doc/refman/5.0/...functions.html
May be I can run a perl or shell script on the file first, to re-format
all the data columns in yyyy-MM-dd format and then run the mysqlimport
command. Please comment.

I use the Date::Manip Perl module for date parsing and reformatting.
See http://search.cpan.org/~sbeck/DateManip-5.44/Manip.pod

Regards,
Bill K.

[Markus Popp]

> Design the table into which you are importing the data so that the field

in the position matching your MM/dd/yy values is a CHAR(10) or similar.

After you import the data successfully, add a date column to the table
(using ALTER TABLE), and copy the values from the CHAR(10) field to the
date field, while parsing the values:

UPDATE myTable
SET dateField = STR_TO_DATE(cha rField, '%m/%d/%y');

Then drop the CHAR(10) field if you wish.

That's exactly what I meant ;-).

Markus