date field importing problems

[theogary]

Our informix database has a date format(MM/DD/YYYY) in all tables. We are trying to import into MySQl. MySQl requires YYYY-MM-DD format. Is there a way to configure Mysql to accept the informix(MM/DD/YYYY)date format.

 

[r937]

import into a "holding table" where the date is defined as char(10)

then use sql to populate the real table while reformatting the date

r937.com | rudy.ca

 

[theogary]

All our informix tables are dumped into text files. This process we dont want to change. I want to change Mysql system date format. Is this possible

 

[r937]

nope

r937.com | rudy.ca

 

[KarveR]

if you add the informix date into a char(10) called tmp_date and your real date should be in real_date , you can use the following to convert the dates:

UPDATE your_table SET real_date = concat(substring(tmp_date,7),"-",substring(tmp_date,1,2),"-",substring(tmp_date,4,2));

This should blitz the lot in a couple of nano seconds, but when you first run it, add LIMIT 1 to the end of the update statement to check it does what you need.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.