Oracle TO_CHAR (Date) Function: A Simple Guide to Formatting Dates

The TO_CHAR function (when used with dates) is one of the most essential and commonly used functions in Oracle SQL. Its job is to take a DATE, TIMESTAMP, or INTERVAL and convert it into a human-readable text string (VARCHAR2) in a format you specify.

This function gives you complete control over how you display dates. You can change 07-NOV-2025 09:30:15 into "11/07/2025", "Friday, November 7th", or "2025-11-07 09:30 AM".

What is the TO_CHAR (Date) Function in Oracle?

The TO_CHAR(datetime, fmt, [nlsparam]) function is a conversion function that "translates" a date value into a string. If you don't provide a format (fmt), Oracle will use your session's default date format (e.g., 'DD-MON-RR'), which is often not what you want for a report.

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

TO_CHAR (Date) Function Syntax

The syntax for TO_CHAR (Date) is:

TO_CHAR(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 that defines the output format.
• [nlsparam] (Optional): A text string to specify a language for names (e.g., NLS_DATE_LANGUAGE = French).

Common Format Models (fmt)

• YYYY: 4-digit year (e.g., 2025)
• MM: 2-digit month (e.g., 11)
• MON: 3-letter month abbreviation (e.g., NOV)
• Month: Full, capitalized month name (e.g., NOVEMBER)
• DD: 2-digit day of the month (e.g., 07)
• Day: Full, capitalized day name (e.g., FRIDAY)
• HH24: 24-hour format (00-23)
• HH or HH12: 12-hour format (01-12)
• MI: Minutes (00-59)
• SS: Seconds (00-59)
• AM or PM: Meridiem indicator

Oracle TO_CHAR (Date) Function Examples

Here are two practical examples of how to use TO_CHAR.

Example 1: Basic Date Formatting with TO_CHAR

This example takes the current system date (SYSDATE) and formats it in several common ways, from a simple US-style date to a long-form timestamp.

Query:

SELECT
  SYSDATE AS "Original_Date",
  TO_CHAR(SYSDATE, 'MM/DD/YYYY') AS "US_Format",
  TO_CHAR(SYSDATE, 'Day, DD Month YYYY') AS "Long_Format",
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS "ISO_Timestamp"
FROM DUAL;

Result: (Assuming the date is Nov 7, 2025 at 2:30 PM)

Original_Date        US_Format   Long_Format                ISO_Timestamp
-------------------- ----------- -------------------------- -------------------
07-NOV-2025 14:30:15 11/07/2025  FRIDAY   , 07 NOVEMBER 2025 2025-11-07 14:30:15

Example 2: Changing the Language of a Date with TO_CHAR

This example shows how to use the optional nlsparam argument to format a date in a different language.

Query:

SELECT
  TO_CHAR(
    DATE '2025-07-14', 
    'Day, DD Month YYYY', 
    'NLS_DATE_LANGUAGE = American'
  ) AS "American_Date",
  
  TO_CHAR(
    DATE '2025-07-14', 
    'Day, DD Month YYYY', 
    'NLS_DATE_LANGUAGE = French'
  ) AS "French_Date"
FROM DUAL;

Result:

American_Date              French_Date
-------------------------- -------------------------
MONDAY   , 14 JULY    2025 VENDREDI, 14 JUILLET 2025