MariaDB TO_CHAR() Function

In MariaDB, TO_CHAR() is a built-in string function that converts a given date/time value to a string in a given format.

MariaDB TO_CHAR() Syntax

Here is the syntax of the MariaDB TO_CHAR() function:

TO_CHAR(v[, fmt])

Parameters

v

Required. The date/time value. You should provide this parameter as a string.

fmt

Optional. The format to convert to. The format string can be any one or a combination of the following:

  • YYYY
  • YYY
  • YY
  • RRRR
  • RR
  • MM
  • MON
  • MONTH
  • MI
  • DD
  • DY
  • HH
  • HH12
  • HH24
  • SS
  • Special characters

The default format is YYYY-MM-DD HH24:MI:SS.

If you provide the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TO_CHAR'.

Return value

The MariaDB TO_CHAR() function returns a string converted from the given date/time value in the given format.

MariaDB TO_CHAR() Examples

Basic example

This statement shows how to use the MariaDB TO_CHAR() function to convert the date 2023-01-01 to a string:

SELECT TO_CHAR('2023-01-01');

Output:

+-----------------------+
| TO_CHAR('2023-01-01') |
+-----------------------+
| 2023-01-01 00:00:00   |
+-----------------------+

In this example, since no fmt parameter given, TO_CHAR() use YYYY-MM-DD HH24:MI:SS as the default output format.

You can also use TO_CHAR() to output a datetime value, as follows:

SELECT TO_CHAR('2023-01-01 01:01:01');

Output:

+--------------------------------+
| TO_CHAR('2023-01-01 01:01:01') |
+--------------------------------+
| 2023-01-01 01:01:01            |
+--------------------------------+

Format

The following statement uses MariaDB TO_CHAR() function to output a datetime value in the YYYY-MM-DD format:

SELECT TO_CHAR('2023-01-01 01:01:01', 'YYYY-MM-DD');

Output:

+----------------------------------------------+
| TO_CHAR('2023-01-01 01:01:01', 'YYYY-MM-DD') |
+----------------------------------------------+
| 2022-12-25                                   |
+----------------------------------------------+

You can also output only the year from a datatime value as follows:

SELECT TO_CHAR('2023-01-01 01:01:01', 'YYYY');

Output:

+----------------------------------------+
| TO_CHAR('2023-01-01 01:01:01', 'YYYY') |
+----------------------------------------+
| 2022                                   |
+----------------------------------------+

Invalid date

If you provide an invalid date, TO_CHAR() will return NULL with a warning:

SELECT TO_CHAR('2023-02-31');

Output:

+-----------------------+
| TO_CHAR('2023-02-31') |
+-----------------------+
| NULL                  |
+-----------------------+

Let’s check out the warnings:

SHOW WARNINGS;

Output:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2023-02-31' |
+---------+------+----------------------------------------+

Invalid format

If you provide an invalid format, TO_CHAR() will return a error:

SELECT TO_CHAR('2023-01-01', 'This is a invalid format');

Output:

ERROR 3047 (HY000): Invalid argument error: date format not recognized at This is a invalid format in function to_char.

Conclusion

In MariaDB, TO_CHAR() is a built-in string function that converts a given date/time value to a string in a given format.