Converting a varchar date field to datetime

[mana2]

Hi,

I was wondering what would be involved in updating a mySQL database that had a date field as varchar changed to datetime. (This was setup by the previous programmer)

If the datetime field is listed as 1977-07-07 and the varchar as 07/07/77 would just changing the field to datetime fix it?

Thanks

 

[r937]

see thread232-642483

 

[QatQat]

create a temp table with same fields but with correct datetime datatype assigned to your date column.

if the column i scalled `date`

Select into temp_tablename
select, field1, field2...etc, concat(substring(date, 7, 2), '-', substring(date, 4, 2), '-', substring(date, 1, 2))

Mysql will insert a 4 digit year although you have only selected a two digit year. After inserting in the temp table, alter permanent table's date field and move all records back.


Bye

Qatqat

Life is what happens when you are making other plans.