Oracle TO_NCHAR (datetime) Function: A Simple Guide

The TO_NCHAR (datetime) function in Oracle SQL is a conversion function that formats DATE or TIMESTAMP values. It works almost identically to the standard TO_CHAR(datetime) function, with one key difference: it returns the formatted string in the national character set (as an NVARCHAR2 data type).

You use this function when you need to display a formatted date in an application or table that specifically requires the national character set (often used for Unicode).

What is the TO_NCHAR (datetime) Function in Oracle?

The TO_NCHAR(datetime, fmt, [nlsparam]) function "translates" a DATE or TIMESTAMP value into a formatted NVARCHAR2 string.

• TO_CHAR(SYSDATE) returns a VARCHAR2 string (database character set).
• TO_NCHAR(SYSDATE) returns an NVARCHAR2 string (national character set).

Like TO_CHAR, its main power comes from the format model (fmt), which is a string of codes that define exactly how the date should be presented.

TO_NCHAR (datetime) Function Syntax

The syntax for TO_NCHAR (datetime) is:

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

Let's break that down:

• datetime: The date or timestamp value (or column) you want to format (e.g., SYSDATE, hire_date).
• [fmt] (Optional): A text string "format model" that defines the output format (e.g., 'YYYY-MM-DD'). If omitted, Oracle uses the default date format for the session.
• [nlsparam] (Optional): A text string to specify a language for month/day names (e.g., 'NLS_DATE_LANGUAGE = French').

Oracle TO_NCHAR (datetime) Function Examples

Here are two practical examples of how to use TO_NCHAR with dates.

Example 1: Converting a DATE with TO_NCHAR

This example shows the difference between TO_NCHAR and TO_CHAR by using the DUMP function. DUMP reveals the internal data type, showing Typ=1 for NVARCHAR2 (national set) and Typ=96 for VARCHAR2 (database set).

Query:

SELECT 
  DUMP(TO_NCHAR(SYSDATE)) AS "NCHAR_Dump",
  DUMP(TO_CHAR(SYSDATE)) AS "CHAR_Dump"
FROM DUAL;

Result: (The exact Len and byte values will vary based on your NLS settings)

NCHAR_Dump                       CHAR_Dump
-------------------------------- -------------------------
Typ=1 Len=18: 0,56,0,45,0,74,...   Typ=96 Len=9: 56,45,74,...

The Typ=1 confirms that TO_NCHAR returned the date in the national character set.

Example 2: Formatting a Date with TO_NCHAR

This example shows that TO_NCHAR uses the same powerful format models as TO_CHAR. Here, we format a timestamp to be more human-readable.

Query:

-- This query simulates an 'orders' table
WITH orders AS (
  SELECT TO_TIMESTAMP('2025-06-26 10:19:43', 'YYYY-MM-DD HH24:MI:SS') AS order_date
  FROM DUAL
)
SELECT 
  TO_NCHAR(order_date, 'Day, DD Month YYYY "at" HH:MI AM') AS "Formatted_Order_Date"
FROM orders;

Result: (The output is a formatted NVARCHAR2 string)

Formatted_Order_Date
------------------------------------------
THURSDAY , 26 JUNE    2025 at 10:19 AM