In MySQL, the DATE_FORMAT() function allows you to format the date and time.

Here’s an example:

SELECT DATE_FORMAT('2018-12-01', '%W, %d %M %Y');

Result:

Saturday, 01 December 2018

In this example, %W is for the weekday name, %d is for the day of the month, %M is for Month, and %Y is for Year. There are many more format specifiers available that enable you to specify a precise format for dates, as well as the time component.

Formatting the Time Component

Here’s an example of formatting the time component:

SELECT DATE_FORMAT('2018-12-01 11:03:15', '%H:%i:%s %p');

Result:

11:03:15 AM

In this example, %p is used to display either AM or PM, whichever is the case.

Of  course, you can combine them together to format both the date and time in one go:

SELECT DATE_FORMAT('2018-12-01 11:03:15', '%H:%i:%s %p, %W, %d %M %Y');

Result:

11:03:15 AM, Saturday, 01 December 2018

Formatting with a Date/Time Function

Often you’ll want to use DATE_FORMAT() along with another date/time function. For example, you could use it along with CURDATE() to return the current date, nicely formatted:

SELECT 
 CURDATE() AS Unformatted, 
 DATE_FORMAT(CURDATE(), '%W, %M %d, %Y') AS Formatted;

Result:

+-------------+----------------------+
| Unformatted | Formatted            |
+-------------+----------------------+
| 2018-05-04  | Friday, May 04, 2018 |
+-------------+----------------------+

You can also do this with the NOW() function if you need the time:

SELECT
 NOW() AS Unformatted,
 DATE_FORMAT(NOW(), '%r') AS Formatted;

Results:

+---------------------+-------------+
| Unformatted         | Formatted   |
+---------------------+-------------+
| 2018-05-04 14:33:52 | 02:33:52 PM |
+---------------------+-------------+

The TIME_FORMAT() Function

MySQL also has a TIME_FORMAT()  function that can be used to format the time. This function works similar to DATE_FORMAT() except that TIME_FORMAT() only accepts format specifiers only for hours, minutes, seconds, and microseconds.

Example:

SET @time = '07:35:10';
 SELECT
 TIME_FORMAT(@time, '%H') AS Hour,
 TIME_FORMAT(@time, '%i') AS Minute,
 TIME_FORMAT(@time, '%s') As Seconds,
 TIME_FORMAT(@time, '%p') AS 'AM or PM';

Results:

+------+--------+---------+----------+
| Hour | Minute | Seconds | AM or PM |
+------+--------+---------+----------+
| 07   | 35     | 10      | AM       |
+------+--------+---------+----------+

The STR_TO_DATE() Function

The STR_TO_DATE() function is the inverse of the DATE_FORMAT() function. It allows you to provide a string to be formatted as a date. It accepts two parameters; the string, and the format.

Here’s an example:

SELECT STR_TO_DATE('Saturday, 6 December 2018', '%W, %d %M %Y') AS Date;

Result:

+------------+
| Date       |
+------------+
| 2018-12-06 |
+------------+

How to Find the Correct Date Format

MySQL has a handy little function called GET_FORMAT(). This function helps you find the correct format to use when using the DATE_FORMAT() function and/or the STR_TO_DATE() function .

Here’s how it works:

SELECT GET_FORMAT(DATE, 'USA');

This results in the following:

%m.%d.%Y

Which tells us the format string we’d need to use when formatting a date with the DATE_FORMAT() function. For example, we could take the results from that example and apply them to the DATE_FORMAT() function:

SELECT DATE_FORMAT(NOW(), '%m.%d.%Y');

And we’d end up with the desired result:

05.04.2018

You can also pass GET_FORMAT() directly to DATE_FORMAT() if you prefer.

SELECT DATE_FORMAT(NOW(), GET_FORMAT(DATE, 'USA'));

Result:

05.04.2018

In any case, below are examples with various options.

Date

SELECT 
    GET_FORMAT(DATE, 'USA') AS USA,
    GET_FORMAT(DATE, 'JIS') AS JIS,
    GET_FORMAT(DATE, 'ISO') AS ISO,
    GET_FORMAT(DATE, 'EUR') AS EUR,
    GET_FORMAT(DATE, 'INTERNAL') AS INTERNAL;

Result:

+----------+----------+----------+----------+----------+
| USA      | JIS      | ISO      | EUR      | INTERNAL |
+----------+----------+----------+----------+----------+
| %m.%d.%Y | %Y-%m-%d | %Y-%m-%d | %d.%m.%Y | %Y%m%d   |
+----------+----------+----------+----------+----------+

Datetime

SELECT 
    GET_FORMAT(DATETIME, 'USA') AS USA,
    GET_FORMAT(DATETIME, 'JIS') AS JIS,
    GET_FORMAT(DATETIME, 'ISO') AS ISO,
    GET_FORMAT(DATETIME, 'EUR') AS EUR,
    GET_FORMAT(DATETIME, 'INTERNAL') AS INTERNAL;

Result:

+-------------------+-------------------+-------------------+-------------------+--------------+
| USA               | JIS               | ISO               | EUR               | INTERNAL     |
+-------------------+-------------------+-------------------+-------------------+--------------+
| %Y-%m-%d %H.%i.%s | %Y-%m-%d %H:%i:%s | %Y-%m-%d %H:%i:%s | %Y-%m-%d %H.%i.%s | %Y%m%d%H%i%s |
+-------------------+-------------------+-------------------+-------------------+--------------+

Time

SELECT 
    GET_FORMAT(TIME, 'USA') AS USA,
    GET_FORMAT(TIME, 'JIS') AS JIS,
    GET_FORMAT(TIME, 'ISO') AS ISO,
    GET_FORMAT(TIME, 'EUR') AS EUR,
    GET_FORMAT(TIME, 'INTERNAL') AS INTERNAL; 

Result:

+-------------+----------+----------+----------+----------+
| USA         | JIS      | ISO      | EUR      | INTERNAL |
+-------------+----------+----------+----------+----------+
| %h:%i:%s %p | %H:%i:%s | %H:%i:%s | %H.%i.%s | %H%i%s   |
+-------------+----------+----------+----------+----------+