# TO\_CHAR

{% hint style="info" %}
`TO_CHAR` is available from [MariaDB 10.6.](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/10.6/what-is-mariadb-106)
{% endhint %}

## Syntax

```sql
TO_CHAR(expr[, fmt])
```

## Description

{% tabs %}
{% tab title="Current" %}
The `TO_CHAR` function converts an *expr* of type [date](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/date), [datetime](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime), [time](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/time) or [timestamp](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/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 `FM`s disables padding.
* An even number of `FM`s 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)
  {% endtab %}

{% tab title="< 12.3" %}
The `TO_CHAR` function converts an *expr* of type [date](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/date), [datetime](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime), [time](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/time) or [timestamp](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/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 `FM`s disables padding.
* An even number of `FM`s enables padding.
  {% endtab %}

{% tab title="< 12.0" %}
The `TO_CHAR` function converts an *expr* of type [date](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/date), [datetime](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime), [time](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/time) or [timestamp](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/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`.
{% endtab %}
{% endtabs %}

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

{% tabs %}
{% tab title="Current" %}

```sql
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   /                                               |
+-----------------------------------------------------------+
```

{% endtab %}

{% tab title="< 12.0" %}

```sql
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                            |
+-----------------------------------------------------+
```

{% endtab %}
{% endtabs %}

## See Also

* [SQL\_MODE=ORACLE](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}