CODEWITHSUNDEEP
mysqlsql
Heather McVay
Heather McVay

Reputation: 949

convert the existing data varchar and column into a DATE

I stored date as a
varchar(256) latin1_swedish_ci

which shows up as: 11/22/2012

Now I wanted to convert the existing data and column to a DATE

What would be the easiest way to do this in SQL

thanks

Upvotes: 0

Views: 1967

Answers (3)

Phoenix
Phoenix

Reputation: 1931

Edit to convert the existing data use:

STR_TO_DATE(t.datestring, '%d/%m/%Y')

Source: Convert String to Date

You may need to create a temp table to hold your data for conversion:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Then you can truncate the table:

TRUNCATE [TABLE] tbl_name

Then you can alter the column datatype:

ALTER TABLE tablename MODIFY columnname DATE;

Then you can reload from the temp table:

INSERT INTO table 
  SELECT temptable.column1, temptable.column2 ... temptable.columnN
  FROM temptable;

Upvotes: 1

dfockler
dfockler

Reputation: 166

Use the STR_TO_DATE function.

Make a new column that is the correct datatype, move the strings from the old column into the new one with the STR_TO_DATE function, and then delete the old column.

UPDATE table SET new_col = STR_TO_DATE(old_col);

MySQL STR_TO_DATE Reference.

Upvotes: 0

exussum
exussum

Reputation: 18550

To be on the safe side I personally would add a new column, Transfer the data and then delete

update `table` set `new_col` = str_to_date( `old_col`, '%m/%d/%Y' ) ;

Check the data is OK before you delete the column.

Upvotes: 6

Related Questions