Converting varchar to date

[Anon]

I found a table with DD-MM-YYYY input, type VARCHAR. Now I want to convert this to regular DATE type MM-DD-YYYY. Who's got the quickest way to do this?

Grtz,
Marco

[Anon]

Stupid me... need to convert to YYYY-MM-DD

[matto]

I haven't tried this out yet, but assuming you use mySQL, have a look at the manual:
http://doc.progipark.com/mysql/manual_Reference.html#ALTER_TABLE

there it says:
"However, as of MySQL Version 3.22.16a, you can also use MODIFY to change a column's type without renaming it:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;"

I think you'll have to clear the dates before altering the column type. 🙁

you could create a temporary table that contains id and date fields.

do a select into ... which saves your actual dates (and relations to original record ids) in that table.

now wipe out the dates in the original table and do the alter table ... as above (!! check first if your mysql supports this!!).

when done, you can load back the dates from the temporary table. (don't know the exact syntax but I know it's possible ...)

note this is only my approach! this procedure is somewhat risky, so check and re-check everything first - maybe someone else has a better idea!

[Anon]

Create one temporary table with column type as Date.
Then insert values from old table to new table using the following statement as
Insert into new_table select to_date (colname,”mm-dd-yyyy”) from old_table. If you get error, pls. try using formatting functions for date in select statement.