TO_CHAR()

In 10.6 ES, 10.6 CS:

Returns a formatted text value from a given date and/or time expression using the given format string.

In 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS, 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:

Not present

See also: Functions in 10.6 ES, in 10.6 CS, in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS

USAGE

TO_CHAR(expr, fmt)

DETAILS

  • TO_CHAR() returns a string representing a formatted date-time value constructed from the specified DATE/DATETIME/TIME/TIMESTAMP expression and the specified format string.

  • TO_CHAR() requires the first argument, but the second argument is optional. The first argument is the date/datetime/time/timestamp expression, and the second argument is the format string.

  • TO_CHAR() returns NULL if any of the arguments are NULL.

PARAMETERS

expr

Required. The value expression for the date/datetime/time/timestamp value to format. String type data is also supported. The value expression is evaluated for each table row that qualifies, and a result returned for each row.

fmt

Optional. The value expression for the format. The format string supports "YYYY/YYY/YY/Y/RRRR/RR/MM/MON/MONTH/MI/DAY/DD/DY/HH/HH12/HH24/SS". Also supports AD/A.D./BC/B.C./AM/A.M./PM/P.M. Supports non-ASCII Special characters. The default format is "YYYY-MM-DD HH24:MI:SS".

EXAMPLES

Simple Example

SELECT TO_CHAR('1776-07-04 12:34:56', 'Day Month DD YYYY HH24:MI') AS result;
+-----------------------------------+
| result                            |
+-----------------------------------+
| Thursday  July      04 1776 12:34 |
+-----------------------------------+

Example Schema and Data

Some of the examples are based on the char_test table:

CREATE TABLE char_test (
  c1 date,
  c2 time,
  c3 datetime,
  c4 timestamp
);
INSERT INTO char_test VALUES
  ('2000-01-15', '12:30:00', '2000-01-15 12:30:00', '2000-01-15 12:30:00'),
  ('2020-01-02', '09:30:00', '2020-01-02 09:30:00', '2020-01-02 09:30:00'),
  ('2021-02-03', '20:30:00', '2021-02-03 20:30:00', '2021-02-03 20:30:00');

Default Format

The default format string for TO_CHAR() is "YYYY-MM-DD HH24:MI:SS":

SELECT TO_CHAR(c1), TO_CHAR(c2),
       TO_CHAR(c3), TO_CHAR(c4)
FROM char_test\G
*************************** 1. row ***************************
TO_CHAR(c1): 2000-01-15 00:00:00
TO_CHAR(c2): 0000-00-00 12:30:00
TO_CHAR(c3): 2000-01-15 12:30:00
TO_CHAR(c4): 2000-01-15 12:30:00
*************************** 2. row ***************************
TO_CHAR(c1): 2020-01-02 00:00:00
TO_CHAR(c2): 0000-00-00 09:30:00
TO_CHAR(c3): 2020-01-02 09:30:00
TO_CHAR(c4): 2020-01-02 09:30:00
*************************** 3. row ***************************
TO_CHAR(c1): 2021-02-03 00:00:00
TO_CHAR(c2): 0000-00-00 20:30:00
TO_CHAR(c3): 2021-02-03 20:30:00
TO_CHAR(c4): 2021-02-03 20:30:00

Use Non-Default Format

To use a non-default format string with TO_CHAR(), provide the format string as the second argument:

SELECT TO_CHAR(c1, 'YYYY-MM-DD'),
       TO_CHAR(c2, 'HH24-MI-SS'),
       TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS'),
       TO_CHAR(c4, 'YY-MM-DD HH24:MI:SS')
FROM char_test\G
*************************** 1. row ***************************
         TO_CHAR(c1, 'YYYY-MM-DD'): 2000-01-15
         TO_CHAR(c2, 'HH24-MI-SS'): 12-30-00
TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS'): 00-01-15 12:30:00
TO_CHAR(c4, 'YY-MM-DD HH24:MI:SS'): 00-01-15 12:30:00
*************************** 2. row ***************************
         TO_CHAR(c1, 'YYYY-MM-DD'): 2020-01-02
         TO_CHAR(c2, 'HH24-MI-SS'): 09-30-00
TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS'): 20-01-02 09:30:00
TO_CHAR(c4, 'YY-MM-DD HH24:MI:SS'): 20-01-02 09:30:00
*************************** 3. row ***************************
         TO_CHAR(c1, 'YYYY-MM-DD'): 2021-02-03
         TO_CHAR(c2, 'HH24-MI-SS'): 20-30-00
TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS'): 21-02-03 20:30:00
TO_CHAR(c4, 'YY-MM-DD HH24:MI:SS'): 21-02-03 20:30:00

ERROR HANDLING

Invalid Argument

The TO_CHAR() function supports only specific values in its format string. When an invalid format string is specified, the operation will fail with the ER_STD_INVALID_ARGUMENT error code:

SELECT TO_CHAR(c1, 'YYYY&MM-DD')
FROM char_test;
ERROR 3047 (HY000): Invalid argument error: date format not recognized at &MM-DD in function to_char.

CHANGE HISTORY

Release Series

History

10.6 Enterprise

  • Added in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Added in MariaDB Community Server 10.6.1.

10.5 Enterprise

  • Not present.

10.5 Community

  • Not present.

10.4 Enterprise

  • Not present.

10.4 Community

  • Not present.

10.3 Enterprise

  • Not present.

10.3 Community

  • Not present.

10.2 Enterprise

  • Not present.

10.2 Community

  • Not present.

EXTERNAL REFERENCES