Converting dates in MySQL

Question

I have a column in a MySQL database that has dates in the form:

Tue Oct 25 2016. I am trying to get it in the form 10/25/2016.

I did some research and tried this:

SELECT DATE_FORMAT(Date, '%d/%m/%Y') FROM table;

But it is returning null

Any help would be greatly appreciated.

Answer 1

Firstly, you will need to convert your date string to MySQL date format ('YYYY-MM-DD'), using STR_TO_DATE function. To convert from string, we have to specify the current format of the date string. In your case, it is '%a %b %d %Y'. Note that the % character is required before format specifier characters.

Details:

• %a Abbreviated weekday name (Sun to Sat)
• %b Abbreviated month name (Jan to Dec)
• %d Day of the month as a numeric value (01 to 31)
• %Y Year as a numeric, 4-digit value

Now, you can utilize DATE_FORMAT function to convert the MySQL date into the desired date string format. In your case, it will be: '%m/%d/%Y'

Details:

• %d Day of the month as a numeric value (01 to 31)
• %m Month name as a numeric value (00 to 12)
• %Y Year as a numeric, 4-digit value

Try the following query:

SELECT DATE_FORMAT(STR_TO_DATE(Date, '%a %b %d %Y'), '%m/%d/%Y') 
FROM table;

Complete list of available format specifiers can be seen at: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format