TO_CHAR

Convert to string. This function converts a value (often date/time) to a string, potentially using a format mask.

TO_CHAR is available from MariaDB 10.6.

Syntax

TO_CHAR(expr[, fmt])

Description

Current

The TO_CHAR function converts an expr of type date, datetime, time or timestamp to a string. The optional fmt argument supports YYY/YYY/YY/RRRR/RR/MM/MON/MONTH/MI/DD/DY/HH/HH12/HH24/SS and special characters. The default value is YYYY-MM-DD HH24:MI:SS. TO_CHAR also accepts FM in the format string, which disables padding of all components following it.

FM can be specified multiple times, with each time disabling the previous state:

• An odd number of FMs disables padding.

• An even number of FMs enables padding.

These additional formats (for fmt) are available:

• FF[1-6] – Fractional seconds

• DDD – Day (1-366)

• IW – Week 1-53 according to ISO 8601

• I – 1-digit year according to ISO 8601

• IY – 2-digit year according to ISO 8601

• IYY – 3-digit year according to ISO 8601

• IYYY – 4-digit year according to ISO 8601

• SYYY – 4-digit year according to ISO 8601 (Oracle can use signed years)

< 12.3

< 12.0

In Oracle, TO_CHAR can also be used to convert numbers to strings, but this is not supported in MariaDB and gives an error.

Examples

Current

SELECT TO_CHAR('1980-01-11 04:50:39', 'YYYY-MM-DD');
+----------------------------------------------+
| TO_CHAR('1980-01-11 04:50:39', 'YYYY-MM-DD') |
+----------------------------------------------+
| 1980-01-11                                   |
+----------------------------------------------+

SELECT TO_CHAR('1980-01-11 04:50:39', 'HH24-MI-SS');
+----------------------------------------------+
| TO_CHAR('1980-01-11 04:50:39', 'HH24-MI-SS') |
+----------------------------------------------+
| 04-50-39                                     |
+----------------------------------------------+

SELECT TO_CHAR('00-01-01 00:00:00', 'YY-MM-DD HH24:MI:SS');
+-----------------------------------------------------+
| TO_CHAR('00-01-01 00:00:00', 'YY-MM-DD HH24:MI:SS') |
+-----------------------------------------------------+
| 00-01-01 00:00:00                                   |
+-----------------------------------------------------+

SELECT TO_CHAR('99-12-31 23:59:59', 'YY-MM-DD HH24:MI:SS');
+-----------------------------------------------------+
| TO_CHAR('99-12-31 23:59:59', 'YY-MM-DD HH24:MI:SS') |
+-----------------------------------------------------+
| 99-12-31 23:59:59                                   |
+-----------------------------------------------------+

SELECT TO_CHAR('9999-12-31 23:59:59', 'YY-MM-DD HH24:MI:SS');
+-------------------------------------------------------+
| TO_CHAR('9999-12-31 23:59:59', 'YY-MM-DD HH24:MI:SS') |
+-------------------------------------------------------+
| 99-12-31 23:59:59                                     |
+-------------------------------------------------------+

SELECT TO_CHAR('21-01-03 08:30:00', 'Y-MONTH-DY HH:MI:SS');
+-----------------------------------------------------+
| TO_CHAR('21-01-03 08:30:00', 'Y-MONTH-DY HH:MI:SS') |
+-----------------------------------------------------+
| 1-January  -Sun 08:30:00                            |
+-----------------------------------------------------+

From MariaDB 12.0, FM removes following padding:

SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'DAY'), '/');
+---------------------------------------------------------+
| CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'DAY'), '/') |
+---------------------------------------------------------+
| /Monday   /                                             |
+---------------------------------------------------------+

SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMDAY'), '/');
+-----------------------------------------------------------+
| CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMDAY'), '/') |
+-----------------------------------------------------------+
| /Monday/                                                  |
+-----------------------------------------------------------+

Even numbers of FM enable padding, while odd numbers disable it:

SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMFMDAY'), '/');
+-------------------------------------------------------------+
| CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMFMDAY'), '/') |
+-------------------------------------------------------------+
| /Monday   /                                                 |
+-------------------------------------------------------------+

SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMFMFMDAY'), '/');
+---------------------------------------------------------------+
| CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMFMFMDAY'), '/') |
+---------------------------------------------------------------+
| /Monday/                                                      |
+---------------------------------------------------------------+

FM only suppresses following padding:

SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'DAYFM'), '/');
+-----------------------------------------------------------+
| CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'DAYFM'), '/') |
+-----------------------------------------------------------+
| /Monday   /                                               |
+-----------------------------------------------------------+

< 12.0

See Also

• SQL_MODE=ORACLE

_{This page is licensed: CC BY-SA / Gnu FDL}