By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,278 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,278 IT Pros & Developers. It's quick & easy.

loading date strings as dates

P: n/a
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

Oct 25 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Oct 25 '05 #2

P: n/a
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


Oct 26 '05 #3

P: n/a
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(charField, '%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.
Oct 27 '05 #4

P: n/a
> 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(charField, '%m/%d/%y');

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


That's exactly what I meant ;-).

Markus
Oct 27 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.