.png?alt=media&token=7e7f4c73-9972-4e67-9a1a-74dc18591022&width=260&dpr=4&quality=100&sign=760f8d93&sv=2){kind=spacer}
.png?alt=media&token=ee5a45b3-d05b-4bb5-97d0-0f25c5eeb110&width=260&dpr=4&quality=100&sign=12bc3702&sv=2){kind=spacer}
TO_CHAR
Syntax
TO_CHAR(expr[, fmt])Description
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. From MariaDB 12.0, 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
In Oracle, TO_CHAR can also be used to convert numbers to strings, but this is not supported in MariaDB and will give an error.
Examples
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 / | +-----------------------------------------------------------+See Also
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?