How to change a date format in mysql-CodePudding

I am getting a null result when trying to change the date format,

 SELECT DATE_FORMAT("31-Mar-2022", '%d/%m/%Y');

What would be the right way of changing the format from 31-Mar-2022 to 2022-03-31 ?

dbfiddle

CodePudding user response：

The route you want to take here in general is STR_TO_DATE to first convert the string date into a bona fide, followed by DATE_FORMAT, to convert the bona fide date back to a string in some other format. So the following will work:

SELECT DATE_FORMAT(STR_TO_DATE('31-Mar-2022', '%d-%b-%Y'), '%Y-%m-%d') AS output;

But note that the default rendering of a MySQL date often will be %Y-%m-%d, so in this case, you probably can avoid the outer call to DATE_FORMAT, and just use:

SELECT STR_TO_DATE('31-Mar-2022', '%d-%b-%Y') AS output;