How the DATE_FORMAT() function works in Mariadb?

The DATE_FORMAT() function is a built-in function in Mariadb that formats a date or datetime value according to a specified format string and returns the result as a new string value.

Posted on

The MariaDB DATE_FORMAT() function is used to display date and time values in different formats. It is a versatile tool for formatting date and time columns in a human-readable form, which is especially useful for reports, data exports, and user interfaces.

Syntax

The syntax for the MariaDB DATE_FORMAT() function is as follows:

DATE_FORMAT(date, format)
  • date is the date or datetime value that you want to format.
  • format is the string that defines how the output should be formatted. it is a string that contains one or more format specifiers that define how the date or datetime value is formatted. The format specifiers are case-sensitive and can be combined in various ways.

Some of the common format specifiers are:

  • %Y: Four-digit year, e.g. 2023
  • %y: Two-digit year, e.g. 23
  • %m: Two-digit month, e.g. 12
  • %M: Month name, e.g. December
  • %d: Two-digit day of the month, e.g. 17
  • %D: Day of the month with English suffix, e.g. 17th
  • %H: Two-digit hour (24-hour format), e.g. 20
  • %h: Two-digit hour (12-hour format), e.g. 08
  • %i: Two-digit minute, e.g. 14
  • %s: Two-digit second, e.g. 30
  • %p: AM or PM, e.g. PM

For a complete list of format specifiers, please refer to the Mariadb documentation.

Examples

Example 1: Standard Date Format

To format a date in the standard YYYY-MM-DD format:

SELECT DATE_FORMAT('2024-03-17', '%Y-%m-%d') AS formatted_date;

The output will be:

+----------------+
| formatted_date |
+----------------+
| 2024-03-17     |
+----------------+

Example 2: Custom Date Format

To format a date in a custom format, such as DD/MM/YYYY:

SELECT DATE_FORMAT('2024-03-17', '%d/%m/%Y') AS formatted_date;

The output will be:

+----------------+
| formatted_date |
+----------------+
| 17/03/2024     |
+----------------+

Example 3: Include Time in Format

To include time in the format:

SELECT DATE_FORMAT('2024-03-17 08:56:29', '%W, %M %d, %Y %h:%i %p') AS formatted_datetime;

The output will be:

+---------------------------------+
| formatted_datetime              |
+---------------------------------+
| Sunday, March 17, 2024 08:56 AM |
+---------------------------------+

Example 4: Abbreviated Month Name

To display the abbreviated month name:

SELECT DATE_FORMAT('2024-03-17', '%b %d, %Y') AS formatted_date;

The output will be:

+----------------+
| formatted_date |
+----------------+
| Mar 17, 2024   |
+----------------+

Example 5: Day of the Week

To display the day of the week:

SELECT DATE_FORMAT('2024-03-17', '%W') AS day_of_week;

The output will be:

+-------------+
| day_of_week |
+-------------+
| Sunday      |
+-------------+

Here are a few functions related to the MariaDB DATE_FORMAT() function:

  • MariaDB STR_TO_DATE() function is used to convert a string into a date or datetime value based on a specified format.
  • MariaDB TIME_FORMAT() function formats the time portion of a time or datetime value as specified.
  • MariaDB UNIX_TIMESTAMP() function returns a Unix timestamp for a date or datetime value.

Conclusion

The DATE_FORMAT() function in MariaDB is an essential tool for anyone working with date and time data. It provides the flexibility to present date and time information in a way that is most appropriate for the application’s users, whether it’s for display purposes or to meet specific formatting requirements for data processing. By mastering the DATE_FORMAT() function, developers can greatly enhance the usability and readability of their database applications.