In MariaDB, TO_CHAR()
is a built-in string function that converts a date/time expression to a string.
The expression can be a date, datetime, time, or timestamp value.
This function was introduced in MariaDB 10.6.1 to enhance Oracle compatibility.
Syntax
The syntax goes like this:
TO_CHAR(expr[, fmt])
Where expr
is a date, datetime, time, or timestamp value, and fmt
is an optional format string that specifies how the output should be formatted.
The format string can be any of the following:
YYYY
YYY
YY
RRRR
RR
MM
MON
MONTH
MI
DD
DY
HH
HH12
HH24
SS
- Special characters
The default value is YYYY-MM-DD HH24:MI:SS
.
Example
Here’s an example to demonstrate:
SELECT TO_CHAR('2020-01-01');
Result:
+-----------------------+ | TO_CHAR('2020-01-01') | +-----------------------+ | 2020-01-01 00:00:00 | +-----------------------+
Pass a Datetime Value
This example uses a datetime value:
SELECT TO_CHAR('2022-12-25 10:30:45');
Result:
+--------------------------------+ | TO_CHAR('2022-12-25 10:30:45') | +--------------------------------+ | 2022-12-25 10:30:45 | +--------------------------------+
Specify a Format String
Here’s an example with a format string:
SELECT TO_CHAR('2022-12-25 10:30:45', 'YYYY-MM-DD');
Result:
+----------------------------------------------+ | TO_CHAR('2022-12-25 10:30:45', 'YYYY-MM-DD') | +----------------------------------------------+ | 2022-12-25 | +----------------------------------------------+
Here’s another one:
SELECT TO_CHAR('2022-12-25', 'DY, DD MONTH YYYY');
Result:
+--------------------------------------------+ | TO_CHAR('2022-12-25', 'DY, DD MONTH YYYY') | +--------------------------------------------+ | Sun, 25 December 2022 | +--------------------------------------------+
Numeric Dates
Here’s what happens when I provide a numeric date:
SELECT TO_CHAR(20200101);
Result:
ERROR 3047 (HY000): Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
Invalid Dates
If the date is invalid, TO_CHAR()
returns null
with a warning:
SELECT TO_CHAR('2020-01-51');
Result:
+-----------------------+ | TO_CHAR('2020-01-51') | +-----------------------+ | NULL | +-----------------------+ 1 row in set, 1 warning (0.001 sec)
Let’s check the warning:
SHOW WARNINGS;
Result:
+---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1292 | Incorrect datetime value: '2020-01-51' | +---------+------+----------------------------------------+
Invalid Format String
If the format string is invalid, TO_CHAR()
returns an error:
SELECT TO_CHAR('2020-01-01', 'wow');
Result:
ERROR 3047 (HY000): Invalid argument error: date format not recognized at wow in function to_char.
Null Format String
TO_CHAR()
returns null
if the format string is null
:
SELECT TO_CHAR('2020-01-01', null);
Result:
+-----------------------------+ | TO_CHAR('2020-01-01', null) | +-----------------------------+ | NULL | +-----------------------------+
Invalid Number of Arguments
Passing an invalid number of arguments (or no arguments) results in an error:
SELECT TO_CHAR();
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TO_CHAR'