At 21:39 -0400 10/14/06, Ferindo Middleton wrote:
Is there a way to change the format of date fields MySQL is expecting when
LOADing data from a file? I have no problem with the format MySQL saves the
date but most spreadsheet programs I use don't make it easy to export text
files with date fields in the format YYYY-MM-DD even if I formated the field
that way on-screen.
It would be great if you could tell MySQL on the command line to expect
dates in the format Month/Day/Year or something like that and be able to
interpret that and convert the date to the format it's expecting on the fly.
If you're using MySQL 5.0 or higher, you can read a column into a user
variable and use SET to reformat the column value before inserting it
into the table. Example:
LOAD DATA LOCAL INFILE 'newdata.txt'
INTO TABLE t (name,@date,value)
SET date = STR_TO_DATE(@date,'%m/%d/%y');
The format string depends on the format of your input data, of course.
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
