VARCHAR to DATE field conversion

pivan

Hi,

simple problem. PHP application with MySQL database, windows platform. I've one plain table, in wich date are stored in a varchar (aaaaargh!), italian timestamp: DD-MM-YYYY

I want to convert some thousands of record date to a DATE field format.

I though there's a way to do this:
create a PHP script that will do this:
- create a correct_table identical to my table except that in this one date are DATE
- read/convert/save all records from table to correct_table.
- verify correct_table, and destroy table
- rename correct_table to table.

I think conceptually it's ok, but I don't know how to start and what's the best way to do this. My first and only fear is to lose data: they're in a production environment, daily used, and I have to do this process unattended (it's difficult to me to reach server location and I've no remote access)

Thanks in advance

Ivan

sfullman

My suggestion,

1) have a second field, only delete the first field when you verify the accuracy of the second

2) run a query like this after you've created NewDate:

UPDATE thisTable SET
NewDate = CONCAT(substring(OldDate,7,4),'-',SUBSTRING(OldDate,4,2),'-',SUBSTRING(OldDate,1,2));

this will give you YYYY-MM-DD;

to verify this (I was looking at
http://www.mysql.com/doc/en/String_functions.html#IDX1231 ), try
SELECT CONCAT(see above..) from thisTable LIMIT 20

and see if the data looks good, then run the UPDATE query.

3) when you're done,delete OldDate

Best,
Sam

pivan

thank you a lot, I'll try it asap

ivan

gderoover

Well, after many more reading especially in dev.mysql.org, i finally came down to find the final answer and solution to my problems for now:-))

it seems that when i manipulate the date in csv, the date where forced in mm/dd/yyyy, initally they were in dd/mm/yyyy spanish. So anyway starting with the original date in mysql in text varchar dd/mm/yyyy, using this syntax, allow you to put out and visualize the new format in yyyy-mm-dd

select id, start_date, str_to_date (start_date,'%d/%m/%Y') as newstart_date, from my_table order by id;

start_date being the old date in text dd/mm/yyyy and of course newstart_date in yyyy-mm-dd

After checking the output is correct to next challenge in to insert the changes in your table.
what i did was to create a new field name newstart_date, date, null, default 0000-00-00 with phpmyadmin, then in sql:

update my_table set newstart_date= str_to_date(start_date,'%d/%m/%Y');

After that just eliminate on phpmyadmin start_date et rename newstart_date to start_date

AND IT WORKS.

I hope this will help

Giro