MySQL supports formatting the datetime values into a different formats using DATE_FORMAT() function. This function accepts date/datetime values as a first parameter and returns into a specific format defined as a second parameter.
Let us explore this with the following examples which are self-explanatory
Define a DATETIME variable
SET @date:='2014-06-16 14:12:49';
-- Display datetime values in YYYY-mm-dd format
SELECT date_format(@date,'%Y-%m-%d') AS formatted_date;
The result is 2014-06-16
-- Display datetime values as Long Date format
SELECT date_format(@date,'%W, %M %d,%Y') AS formatted_date;
The result is Monday, June 16,2014
-- Display datetime values as Full date format
SELECT date_format(@date,'%W, %M %d,%Y %T') AS formatted_date;
The result is Monday, June 16,2014 14:12:49
-- Display datetime values in HH:MM:SS format
SELECT date_format(@date,'%T') AS formatted_date;
The result is 14:12:49
-- Display datetime values in Month Year format
SELECT date_format(@date,'%M %Y')Â Â AS formatted_date;
The result is June 2014
-- Display datetime values in mm-dd-yyyy format
SELECT date_format(@date,'%m-%d-%Y')Â Â AS formatted_date;
The result is 06-16-2014
-- Display datetime values in dd-mm-yyyy format
SELECT date_format(@date,'%M %Y')Â Â AS formatted_date;
The result is 16-06-2014
Reference:Â Pinal Dave (https://blog.sqlauthority.com)