Squall Lionheart Squall Lionheart - 2 months ago 6
MySQL Question

Rearrange int value from MMDDYYY to YYYYMMDD using MySQL

I have a VARCHAR data type in field name date:

DATE

09282016
09272016


I want to arrange it to:

DATE
20160928
20160927


How can I do that in MySQL?

Answer

With the help of STR_TO_DATE and DATE_FORMAT function you can achieve this.

Select Query:

SELECT 
DATE_FORMAT(STR_TO_DATE(your_date_column,'%m%d%Y'),'%Y%m%d')
FROM your_table;

Update Query:

UPDATE 
your_table 
SET your_date_column = DATE_FORMAT(STR_TO_DATE(your_date_column,'%m%d%Y'),'%Y%m%d');

Demonstration:

SET @str := '09282016';

SELECT 
DATE_FORMAT(STR_TO_DATE(@str,'%m%d%Y'),'%Y%m%d') AS output;

Output
20160928

Date should be stored in date datatype.