CODEWITHSUNDEEP
mysql
Pratik Prajapati
Pratik Prajapati

Reputation: 345

Convert varchar column to date in mysql at database level

I have one column date1 which is varchar type I want this column to date type. I tried changing field but all date is converted to 0000-00-00. format is dd-mm-yyyy but in varchar.

How can I convert the same date format but with date format using sql queries or similar but at database level ?

Upvotes: 21

Views: 39550

Answers (3)

PeteW
PeteW

Reputation: 731

The other answers here are risky, because if they go wrong you'll lose your data. A safer way to do this is to create a new field on your database with a DATE (or DATETIME if you need time as well) format, then to run a query like

UPDATE `table` SET `my_new_date_field` = STR_TO_DATE( `my_old_data_field`, '%d/%m/%Y');

In this way, if the %d/%m/%Y bit is wrong, you won't lose your data.

Once you're happy, you can delete the old data field and rename the new one.

Upvotes: 19

Dhruv Patel
Dhruv Patel

Reputation: 404

use STR_TO_DATE Function of MySQL

FIRST you will need to update the value in date format.

UPDATE `tbl` SET `date1` = STR_TO_DATE(`date1`, '%d-%m-%Y') WHERE 1=1

THEN Convert the field to date.

Most importantly remember to insert date as Y-m-d format, after then.

Upvotes: 5

hjpotter92
hjpotter92

Reputation: 80639

UPDATE `table`
SET `column` = str_to_date( `column`, '%d-%m-%Y' );

More about STR_TO_DATE function.

Since your column name is date1, you can replace column with date1 in the above syntax, and the code shall be:

UPDATE `table`
SET `date1` = str_to_date( `date1`, '%d-%m-%Y' );

Upvotes: 35

Related Questions