How to display string as date?

Question

I have a column(string (8)) that contains data like 12032008 and I have to display it as 12/03/2008. What should I do in retrieve query?

Ideally you convert it to a `DATE` column and then do the date formatting client-side in whatever quirky date format the user wants. — tadman, Dec 19 '17 at 06:24
Does `12/03/2008` mean December 3rd, 2008, or March 12, 2008? — Tim Biegeleisen, Dec 19 '17 at 06:31

Tim Biegeleisen · Answer 1 · 2017-12-19T06:36:24.740

The most appropriate thing for you to do here would be to stop storing date information in text columns. Instead, put 12032008 into a bona fide datetime column. This will make your life easiest in the long run. If you can't do that in the short term, here are two options for you to consider.

The short way, just using base string functions:

SELECT
    CONCAT(LEFT(col, 2), '/', SUBSTRING(col, 3, 2), '/', RIGHT(col, 4)) AS output
FROM yourTable;

The longer way, and probably the better long term solution, would be to first convert your date string into a date, then convert it back to a string using the formatting you want:

SELECT
    DATE_FORMAT(STR_TO_DATE(col, '%d%m%Y'), '%d/%m/%Y') AS output
FROM yourTable;

Demo

score 2 · Answer 2 · answered Dec 19 '17 at 06:26

In mysql first convert it to date object using STR_TO_DATE then to format use DATE_FORMAT

SELECT DATE_FORMAT(STR_TO_DATE('12032008','%d%m%Y'),'%d/%m/%Y')

But ideally you should not save the date or date time as strings in table it will make things complex for mysql and for you also

Demo

How to display string as date?

2 Answers2

Demo