CODEWITHSUNDEEP
mysql
Steve
Steve

Reputation: 1672

How do i convert a varchar field to date

I have a dob attribute which is a varchar. Some of the dates are stored in format 6/6/2017 and some in format 2017-06-06. How do i convert those in format 6/6/2017 into 2017-06-06 and set the dob field as DATE datatype?

Upvotes: 0

Views: 73

Answers (3)

Samir Selia
Samir Selia

Reputation: 7065

First update existing column to proper date format (YYYY-MM-DD) using 

UPDATE tbl_name SET dob = STR_TO_DATE(dob,'%d/%m/%Y')
WHERE STR_TO_DATE(dob,'%d/%m/%Y') IS NOT NULL;

Once Update is completed ALTER the column to DATE datatype

ALTER TABLE tbl_name MODIFY `dob` DATE NOT NULL DEFAULT '0000-00-00';

Upvotes: 1

Hardika Satasiya
Hardika Satasiya

Reputation: 354

For Your query you can write like this: 

UPDATE table_name
    SET column1=DATE(STR_TO_DATE(column1, '%Y-%m-%d'))
    WHERE column1='"'.date('%d/%m/%Y',strtotime(column1)).'"'

and to set varchar to date datatype you can use CONVERT function or write like:

SET DATEFORMAT dmy

Upvotes: 2

Chintan7027
Chintan7027

Reputation: 7605

If you wants to convert the D/M/YYYY date to YYYY-MM-DD permanently then add_new column having DATE datatype and update that column value by using this query

UPDATE TABLE tbl_name set format_dob= STR_TO_DATE(yourdatefield, '%d/%m/%Y')

If you just wants the calculated column then simply use

SELECT STR_TO_DATE(yourdatefield, '%m/%d/%Y') FROM tbl_name

Upvotes: 1

Related Questions