Oracle TO_CHAR(datetime) Function

Oracle TO_CHAR(datetime) is a built-in function that converts a given date, time, or interval value to a string based on a specified format.

Oracle TO_CHAR(datetime) Syntax

Here is the syntax for the Oracle TO_CHAR(datetime) function:

TO_CHAR(expr [, fmt [, 'nlsparam' ] ])

Parameters

expr

Required. A date-time or interval expression. It can be of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type.

fmt

Optional. A formatting string.

'nlsparam'

Optional. You can use this parameter in the form of 'NLS_DATE_LANGUAGE = language', where language is the language name.

Return Value

The Oracle TO_CHAR(datetime) function returns a string of VARCHAR2 data type.

If fmt is omitted, date is converted to a VARCHAR2 value as follows:

  • DATE values are converted to values of the default date format.
  • TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values of the default timestamp format.
  • TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp format with time zone.
  • Interval values are converted to the numeric representation of the interval in interval text.

If any argument is NULL, TO_CHAR(datetime) returns NULL.

Oracle TO_CHAR(datetime) Examples

Here are some examples that demonstrate the usage of the Oracle TO_CHAR(datetime) function.

Basic Usage

SELECT
    TO_CHAR(DATE '2023-02-13', 'YYYY/MM/DD')
FROM dual;

Output:

TO_CHAR(DATE'2023-02-13','YYYY/MM/DD')
_________________________________________
2023/02/13

Or you can specify the time part of the output:

SELECT
    TO_CHAR(DATE '2023-02-13', 'YYYY/MM/DD HH24:MI:SS')
FROM dual;

Output:

TO_CHAR(DATE'2023-02-13','YYYY/MM/DDHH24:MI:SS')
___________________________________________________
2023/02/13 00:00:00

Interval

The Oracle TO_CHAR(datetime) function allows you to output an interval value:

SELECT
    TO_CHAR(INTERVAL '25-2' YEAR TO MONTH) "Year To Month",
    TO_CHAR(INTERVAL '2 23:59:59' DAY TO SECOND) "Day To Second"
FROM dual;

Output:

Year To Month    Day To Second
________________ ______________________
+25-02           +02 23:59:59.000000

This example uses the default format parameter.

Language Setting

The Oracle TO_CHAR(datetime) function allows you to specify a language to display the month or day of the week according to the set language.

SELECT
    TO_CHAR(
        DATE '2023-02-13',
        'DY, DD MONTH YYYY',
        'NLS_DATE_LANGUAGE = English'
    ) "English",
    TO_CHAR(
        DATE '2023-02-13',
        'DY, DD MONTH YYYY',
        'NLS_DATE_LANGUAGE = German'
    ) "German"
FROM dual;

Output:

English                   German
_________________________ ________________________
MON, 13 FEBRUARY  2023    MO, 13 FEBRUAR   2023

NULL parameter

If any parameter is NULL, TO_CHAR(datetime) will return NULL.

SET NULL 'NULL';
SELECT
    TO_CHAR(NULL, NULL, NULL)
FROM dual;

Output:

TO_CHAR(NULL,NULL,NULL)
__________________________
NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle TO_CHAR(datetime) is a built-in function that converts a given date, time, or interval value to a string based on a specified format.