如何从mysql中的字符串日期中提取日期格式？

Question

Suppose i have a table test which has a column mydate varchar type and it stores different date formats, below is a sample date format:

now i want to write a query for above id = 1 which will return me the output like:

1 | d M Y

So my requirement is to get the date format in the query response like above, understand one more below example

For Example:

I have 12 June 1999 stored in column so expected output:

d M Y

I searched alot for this i did not get the answer, i am not getting what to try like

select id, ..(main thing that i am not getting)... from test where id = 1

please help if any one knows this

Answer 1

By using STR_TO_DATE you can achieve this.

select id,DATE_FORMAT(STR_TO_DATE(date, '%d %M %Y'),'%d %M %Y') as date from test