CODEWITHSUNDEEP
mysqldatecopy
BigRedEO
BigRedEO

Reputation: 847

Copy values one MySQL column to another, but formatted

I have added a DATE column to a table, but now need to populate that DATE column with the values from another column - except that original column is an INT. The INT column is mmddyyyy. Is there a way to copy and format using 

UPDATE `table` SET int_column = date_column

Upvotes: 0

Views: 195

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Try this using str_to_date and lpad functions:

UPDATE `table` SET date_column = str_to_date(lpad(int_column, 8, 0),'%m%d%Y')

Why used lpad(int_column, 8, 0) - When date is, say, 02012017, the direct cast to char will convert it into 2012017, for which str_to_date function will return null. Lpad pads required 0 to make length 8 and hence outputs 02012017 which str_to_date function will correctly convert.

Upvotes: 1

Related Questions