MariaDB DATE_FORMAT() Function

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

MariaDB DATE_FORMAT() Syntax

This is the syntax of the MariaDB DATE_FORMAT() function:

DATE_FORMAT(date, format)

Parameters

date

Required. The date that needs to be formatted.

format

Required. Formatting pattern string.

The following table organizes the formatting symbols available in format:

symbol illustrate
%a Abbreviation of the week name (Sun.. Sat)
%b Abbreviation of the month name (Jan.. Dec)
%c month number (0.. 12)
%D Day of the month with English prefixes (0th, 1st, 2nd, 3rd, …)
%d Two-digit representation of day of the month (00.. 31)
%e Numerical representation of each day of the month (0.. 31)
%f microseconds (000000.. 999999)
%H hours (00.. 23)
%h hours (01.. 12)
%I hours (01.. 12)
%i minutes (00.. 59)
%j day of the year (001.. 366)
%k hours (0.. 23)
%l hours (1.. 12)
%M month name (January.. December)
%m Two-digit month (00.. 12)
%p AM or PM
%r Twelve-hour time (hh:mm:ss followed by AM or PM)
%S seconds (00.. 59)
%s seconds (00.. 59)
%T Twenty-four hour time (hh:mm:ss)
%U The week of the year (00.. 53), week starts on Sunday; WEEK() mode 0 in the function
%u The week of the year (00.. 53), the week starts on Monday; WEEK() mode 1 in the function
%V The week of the year (01.. 53), the week starts on Sunday; WEEK() mode 2 in the function, for%X
%v The week of the year (01.. 53), the week starts on Monday; WEEK() mode 3 in the function, for%x
%W the name of the week (Sunday.. Saturday)
%w Day of the week (0=Sunday.. 6=Saturday)
%X The week of the year, starting with Sunday, four digits, used for%V
%x The week of the year, starting with Monday, four digits, used for%v
%Y four digit year
%y two digit year
%% escape%
%x x, other characters listed above

You can view all date formatting symbols available in MariaDB .

If you provide no parameters or the wrong number of parameters, MariaDB will report an error: 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.

Return value

The MariaDB DATE_FORMAT() function formats the date and time according to the specified format and returns the formatted string.

The DATE_FORMAT() function will return NULL if any of the arguments are NULL.

MariaDB DATE_FORMAT() Examples

Format date

This statement shows how to use MariaDB DATE_FORMAT() function to format date values:

SELECT
    DATE_FORMAT('2023-01-10', '%Y'),
    DATE_FORMAT('2023-01-10', '%W'),
    DATE_FORMAT('2023-01-10', '%M %d, %Y'),
    DATE_FORMAT('2023-01-10', '%M %e %Y'),
    DATE_FORMAT('2023-01-10', '%W, %M %e, %Y')\G

Output:

           DATE_FORMAT('2023-01-10', '%Y'): 2023
           DATE_FORMAT('2023-01-10', '%W'): Tuesday
    DATE_FORMAT('2023-01-10', '%M %d, %Y'): January 10, 2023
     DATE_FORMAT('2023-01-10', '%M %e %Y'): January 10 2023
DATE_FORMAT('2023-01-10', '%W, %M %e, %Y'): Tuesday, January 10, 2023

Format datetime

This statement shows how to use MariaDB DATE_FORMAT() function to format datetime values:

SELECT DATE_FORMAT('2023-01-10 13:14:15', '%W, %M %e %Y %r');

Output:

+-------------------------------------------------------+
| DATE_FORMAT('2023-01-10 13:14:15', '%W, %M %e %Y %r') |
+-------------------------------------------------------+
| Tuesday, January 10 2023 01:14:15 PM                  |
+-------------------------------------------------------+

Conclusion

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