# DATE\_FORMAT
## Syntax
```sql
DATE_FORMAT(date, format[, locale])
```
## Description
Formats the date value according to the format string.
The language used for the names is controlled by the value of the [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names) system variable. See [server locale](https://mariadb.com/docs/server/reference/data-types/string-data-types/character-sets/internationalization-and-localization/server-locale) for more on the supported locales.
### Date Formatting Options
The options that can be used by `DATE_FORMAT()`, as well as its inverse [STR\_TO\_DATE](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)() and the [FROM\_UNIXTIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/from_unixtime) function, are:
| Option | Description |
| ------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| %a | Short weekday name in current locale (Variable [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names)). |
| %b | Short form month name in current locale. For locale `en_US` this is one of: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, or Dec. |
| %c | Month with 1 or 2 digits. |
| %D | Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...). |
| %d | Day with 2 digits. |
| %e | Day with 1 or 2 digits. |
| %f | [Microseconds](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/microseconds-in-mariadb) 6 digits. |
| %H | Hour with 2 digits between 00-23. |
| %h | Hour with 2 digits between 01-12. |
| %I | Hour with 2 digits between 01-12. |
| %i | Minute with 2 digits. |
| %j | Day of the year (001-366) |
| %k | Hour with 1 digits between 0-23. |
| %l | Hour with 1 digits between 1-12. |
| %M | Full month name in current locale (Variable [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names)). |
| %m | Month with 2 digits. |
| %p | AM/PM according to current locale (Variable [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names)). |
| %r | Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'. |
| %S | Seconds with 2 digits. |
| %s | Seconds with 2 digits. |
| %T | Time in 24 hour format. Short for '%H:%i:%S'. |
| %U | Week number (00-53), when first day of the week is Sunday. |
| %u | Week number (00-53), when first day of the week is Monday. |
| %V | Week number (01-53), when first day of the week is Sunday. Used with %X. |
| %v | Week number (01-53), when first day of the week is Monday. Used with %x. |
| %W | Full weekday name in current locale (Variable [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names)). |
| %w | Day of the week. 0 = Sunday, 6 = Saturday. |
| %X | Year with 4 digits when first day of the week is Sunday. Used with %V. |
| %x | Year with 4 digits when first day of the week is Monday. Used with %v. |
| %Y | Year with 4 digits. |
| %y | Year with 2 digits. |
| %Z | Timezone abbreviation. From [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0). |
| %z | Numeric timezone +hhmm or -hhmm presenting the hour and minute offset from UTC. From [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0). |
| %# | For [str\_to\_date](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)(), skip all numbers. |
| %. | For [str\_to\_date](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)(), skip all punctation characters. |
| %@ | For [str\_to\_date](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)(), skip all alpha characters. |
| %% | A literal % character. |
To get a date in one of the standard formats, [GET\_FORMAT()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/get_format) can be used.
## Examples
```sql
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------+
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+
SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277 |
+------------------------------------------------------------+
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+------------------------------------------------------------+
SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52 |
+------------------------------------+
SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00 |
+---------------------------------+
```
Optionally, the locale can be explicitly specified as the third `DATE_FORMAT()` argument. Doing so makes the function independent from the session settings, and the three argument version of `DATE_FORMAT()` can be used in virtual indexed and persistent [generated-columns](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/generated-columns):
```sql
SELECT DATE_FORMAT('2006-01-01', '%W', 'el_GR');
+------------------------------------------+
| DATE_FORMAT('2006-01-01', '%W', 'el_GR') |
+------------------------------------------+
| Κυριακή |
+------------------------------------------+
```
Format the current date and time in German date-time format:
```sql
SELECT DATE_FORMAT(NOW(),'%W, %e. %M %Y, %k:%i Uhr (%Z)','de_DE');
+------------------------------------------------------------+
| DATE_FORMAT(NOW(),'%W, %e. %M %Y, %k:%i Uhr (%Z)','de_DE') |
+------------------------------------------------------------+
| Donnerstag, 5. Februar 2026, 20:50 Uhr (CET) |
+------------------------------------------------------------+
```
{% hint style="info" %}
Timezone information from MariaDB 11.3:
{% endhint %}
```sql
SELECT DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z');
+--------------------------------------------------+
| DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z') |
+--------------------------------------------------+
| Wednesday 20 September 2023 15:00:23 SAST +0200 |
+--------------------------------------------------+
```
## See Also
* [STR\_TO\_DATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)
* [FROM\_UNIXTIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/from_unixtime)
*This page is licensed: GPLv2, originally from* [*fill\_help\_tables.sql*](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)
{% @marketo/form formId="4316" %}
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date_format.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.