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.