TO_CHAR()

Overview

In 10.6 ES:

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

In 10.5 ES, 10.4 ES:

Not present

See also: Functions in 10.6 ES, in 10.5 ES, and in 10.4 ES

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.5 Enterprise

  • Not present.

10.4 Enterprise

  • Not present.

EXTERNAL REFERENCES