I have dates in integer format in a column. The length is 11. Example values current format integer (11) --> date format required

• yyyymmdd --> dd/mm/yyyy
• 20121203 --> 03/12/2012
• 20090403 --> 03/04/2009

Can someone suggest a solution keeping in mind that the change need to reflect across the entire column in the table?

CodePudding user response：

Docummentation: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

DATE_FORMAT("20121203", "%d/%m/%Y")

CodePudding user response：

Use STR_TO_DATE :

select STR_TO_DATE(col1, "%Y%m%d") as my_date
from test_tbl;

Result:

my_date
2012-12-03
2009-04-03

Demo

Or DATE_FORMAT as previous answer:

select DATE_FORMAT(col1, "%d/%m/%Y") as my_date
from test_tbl;

Result:

my_date
03/12/2012
03/04/2009

Demo

Maybe using both:

select DATE_FORMAT(STR_TO_DATE(col1, "%Y%m%d"),'%d/%m/%Y') as my_date
from test_tbl;

my_date
03/12/2012
03/04/2009

Demo