In MariaDB, DATE_FORMAT() is a built-in date and time function that formats a date according to the given format string.

It requires two arguments; the date and the format string. It also accepts an optional third argument that allows you to specify the locale.

Syntax

The syntax goes like this:

DATE_FORMAT(date, format[, locale])

Where date is the date, format is the format string (see accepted format specifiers), and locale is an optional locale to use for the returned format.

Example

Here’s an example:

SELECT DATE_FORMAT('2030-01-25 10:30:45', '%W, %D %M %Y');

Result:

+----------------------------------------------------+
| DATE_FORMAT('2030-01-25 10:30:45', '%W, %D %M %Y') |
+----------------------------------------------------+
| Friday, 25th January 2030                          |
+----------------------------------------------------+

Here it is again, but this time using %r to return the time returned in 12 hour format:

SELECT DATE_FORMAT('2030-01-25 10:30:45', '%r');

Result:

+------------------------------------------+
| DATE_FORMAT('2030-01-25 10:30:45', '%r') |
+------------------------------------------+
| 10:30:45 AM                              |
+------------------------------------------+

We can combine them, so that we get the time and date:

SELECT DATE_FORMAT('2030-01-25 10:30:45', '%r %W, %D %M %Y');

Result:

+-------------------------------------------------------+
| DATE_FORMAT('2030-01-25 10:30:45', '%r %W, %D %M %Y') |
+-------------------------------------------------------+
| 10:30:45 AM Friday, 25th January 2030                 |
+-------------------------------------------------------+

The language for the month names, day names, etc are determined by the lc_time_names system variable. The default is always en_US regardless of the system’s locale setting. Here’s how to see your current setting.

The Locale Argument

Starting with MariaDB 10.3.2, an optional third argument can be used to specify the locale. When this is specified, it makes the function independent from the session settings.

SELECT DATE_FORMAT('2030-01-25', '%W, %D %M %Y', 'es_PR');

Result:

+----------------------------------------------------+
| DATE_FORMAT('2030-01-25', '%W, %D %M %Y', 'es_PR') |
+----------------------------------------------------+
| viernes, 25th enero 2030                           |
+----------------------------------------------------+

Here are some more locales:

SELECT 
    DATE_FORMAT('2030-01-25', '%W, %D %M %Y', 'fr_FR') AS fr_FR,
    DATE_FORMAT('2030-01-25', '%W, %D %M %Y', 'hr_HR') AS hr_HR,
    DATE_FORMAT('2030-01-25', '%W, %D %M %Y', 'ms_MY') AS fr_FR,
    DATE_FORMAT('2030-01-25', '%W, %D %M %Y', 'th_TH') AS th_TH;

Result:

fr_FR: vendredi, 25th janvier 2030
hr_HR: Petak, 25th Siječanj 2030
fr_FR: Jumaat, 25th Januari 2030
th_TH: ศุกร์, 25th มกราคม 2030

In this case, the last one is Thai, and although it uses Thai characters for the month name and day name, it doesn’t change the year to the Thai calendar. The year 2030 would be 2573 when using the Thai calendar. So I guess the assumption is that if you want to use the Thai year, then the date you pass will already use the Thai year.

Current Date

Here, we pass NOW() as the date argument in order to format the current date:

SELECT DATE_FORMAT(NOW(), '%r %W, %D %M %Y');

Result:

+---------------------------------------+
| DATE_FORMAT(NOW(), '%r %W, %D %M %Y') |
+---------------------------------------+
| 09:53:00 AM Tuesday, 11th May 2021    |
+---------------------------------------+

Invalid Arguments

When passed any invalid arguments, DATE_FORMAT() returns null:

SELECT DATE_FORMAT('Homer', 'Simpson');

Result:

+---------------------------------+
| DATE_FORMAT('Homer', 'Simpson') |
+---------------------------------+
| NULL                            |
+---------------------------------+

Missing Argument

Calling DATE_FORMAT() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT DATE_FORMAT();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

And another example:

SELECT DATE_FORMAT('2030-05-21');

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

The GET_FORMAT() Function

You can also use the GET_FORMAT() function in the second argument to DATE_FORMAT(). This returns the full format string for a given date format, which saves you from having to remember the format string to use.