TO_CHAR()

This page is part of MariaDB's Documentation.

The parent of this page is: Functions for MariaDB Enterprise Server

Overview

In 11.4 ES, 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 11.4 ES, 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.5 CS, 10.4 ES, 10.4 CS, 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:: Not present

In 10.5 ES, 10.4 ES:: Not present

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.

SYNONYMS

SCHEMA

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".

SKYSQL

PRIVILEGES

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.

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series	History
11.4 Enterprise	Not present.
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.

Release Series	History
11.4 Enterprise	Not present.
10.6 Enterprise	Added in MariaDB Enterprise Server 10.6.4-1.
10.5 Enterprise	Not present.
10.4 Enterprise	Not present.

EXTERNAL REFERENCES

Additional information on this topic may be found in the MariaDB Public Knowledge Base.