Oracle TO_NCHAR(datetime) Function

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

Unlike TO_CHAR(datetime), its return value is in the national character set.

Oracle `TO_NCHAR(datetime)` Syntax

Here’s the syntax of the Oracle TO_NCHAR(datetime) function:

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

Parameters

expr: Required. A date-time or interval expression. It can be a 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 format 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_NCHAR(datetime) function returns a VARCHAR2 type string.

If fmt is omitted, then date is converted to the following VARCHAR2 values:

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 interval literals.

If any of the arguments is NULL, TO_NCHAR(datetime) returns NULL.

Oracle `TO_NCHAR(datetime)` Examples

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

Basic Usage

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

输出：

TO_NCHAR(DATE'2023-02-13','YYYY/MM/DDHH24:MI:SS')
____________________________________________________
2023/02/13

Or you can specify the time part of the output:

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

输出：

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

Interval

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

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

输出：

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

This example uses the default format parameter.

Language Settings

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

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

输出：

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

NULL Parameter

If any of the parameters are NULL, TO_NCHAR(datetime) will return NULL.

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

输出：

TO_NCHAR(NULL,NULL,NULL)
__________________________
NULL

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

Conclusion

The Oracle TO_NCHAR(datetime) is a built-in function that converts a given date, time, or interval value to a string according to the specified format.

Oracle TO_NCHAR(datetime) Syntax