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.

"What is type of your date column?" @MaximFedorov i assume when i see a format Tue Oct 25 2016 it's one of the string types varchar or char — Raymond Nijland
– Raymond Nijland, Commented Sep 17, 2018 at 13:38
The datatype of the date column is text. Should I change it to something else? Thanks for the help. I was given this dataset so I didn't want to change anything until I had to. Looks like I might have to now — miltonjbradley
– miltonjbradley, Commented Sep 17, 2018 at 13:40
"The datatype of the date column is text. Should I change it to something else?" Yes because TEXT is stored separated from the table data and requires one extra random disk I/O per record (meaning 4 - 6ms extra time on normal HDD disk) to fetch the TEXT column atleast that's the case with InnoDB engine.. Besides you should choose a MySQL datatype that best matches for data for dates this is the DATE datatype — Raymond Nijland
– Raymond Nijland, Commented Sep 17, 2018 at 13:57

Madhur Bhaiya · Accepted Answer · 2018-10-20 14:17:23Z

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

Collectives™ on Stack Overflow

Converting dates in MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related