If the data is on a spreadsheet, then why not use excel to rewrite
the date?
For example, copy the text where the dates are,...then format the
entire column to be "text" and not a date (because excel stores dates
as numbers,...but displays them as dates),...then paste the dates
back into the column,...now using some other columns, you can create
the format you want,...
use this in a cell and it'll pull the 3 characters out that are the
Month (ie Jan, Feb, Mar, etc)
=mid(A1,4,3)
use this (extend it to December) in another cell to translate the
month into the numbers,...
=IF(B1="Jan","01",IF(B1="Feb","02",IF(B1="Mar","03",IF
(B1="Apr","04",IF(B1="May","05")))))
Do something similar to translate your year into a 4 digit (or before
reformatting the original data, have excel display it with a 4 digit
year, then copy it, reformat to text, and paste it back).
Then you can pull them all together in another cell,...
=C1 & "-" & D1 & "-" & right(A1,2)
Look at the functions left, right, mid to see how to pull out parts,...
Jeff
*****************************************
Jeff Mao
Coodinator of Educational Technology
Maine Department of Education
23 State House Station • Augusta, ME 04333
(207) 624-6634 (office) • (207) 462-4137 (mobile)
[EMAIL PROTECTED]
On May 31, 2005, at 5:23 AM, James M. Gonzalez wrote:
Hello list!
Im loading rows from a txt tab separated fields file into a MySQL
table.
It mostly works, but the date format in one field is not the type that
MySQL likes. So right now Im loading it as a char field. Yes, it is
ugly
and a pain to work with. I would like to some how automate it and
change
the time format.
From -> 30-May-05
To -> 2005-05-30
The txt file is initially generated from an excel sheet.
Im using: MySQL 4.1.11 on Red Hat 9 with kernel 2.4.20
Any ideas?
James Collado
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]