How TO_CHAR() Works in MariaDB

by

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'