In Oracle Database, the TO_CHAR(datetime) function converts a datetime or interval value to a VARCHAR2 value in the format specified by the date format.

Syntax

The syntax goes like this:

TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

Where:

• datetime can be a value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE data type
• interval can be a value of INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type
• fmt is an optional format model that specifies how the result should be formatted
• 'nlsparam' is an optional argument that specifies the language in which month and day names and abbreviations are returned.

Example

Here’s an example to demonstrate:

SELECT TO_CHAR(DATE '2035-09-26', 'DY, DD MONTH YYYY')
FROM DUAL;

Result:

WED, 26 SEPTEMBER 2035

So, it returned the date that was provided in the first argument, in the format specified by the second argument.

The second argument provides the format model. The format model can consist of one or more format elements. For example, DY is a format element, as is DD, MONTH, etc.

See this Full List of Datetime Format Elements in Oracle for a list of format elements that can be used to format datetime values with this function.

Default Format

If you omit the fmt argument, the result will be converted as follows:

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

Here’s an example of a converting a DATE value without specifying the format:

SELECT TO_CHAR(DATE '2035-09-26')
FROM DUAL;

Result:

26/SEP/35

In this case, my session’s default date format is DD/MON/RR, and so the result reflects that. I know that this is my session’s default date format because I queried the V$NLS_PARAMETERS view, which shows me the current value of the NLS parameters.

See How to Check the Values of the NLS Parameters if you need to check them.

Also see How to Change your Session’s Date Format if you want to change the default datetime format for your current session.

Interval

Here’s an example that outputs an interval value in the default format:

SELECT TO_CHAR(INTERVAL '25-2' YEAR TO MONTH)
FROM DUAL;

Result:

+25-02

The 'nlsparam' Argument

The 'nlsparam' argument specifies the language in which month and day names and abbreviations are returned. This argument can have the following form:

'NLS_DATE_LANGUAGE = language'

Example:

SELECT 
    TO_CHAR(
        DATE '2035-09-26', 
        'DY, DD MONTH YYYY',
        'NLS_DATE_LANGUAGE = SPANISH'
    )
FROM DUAL;

Result:

MIÉ, 26 SEPTIEMBRE 2035

Null Arguments

Passing null results in null:

SET NULL 'null';
SELECT TO_CHAR(null)
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Missing Argument

Calling the function without passing any arguments, results in an error:

SELECT TO_CHAR()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TO_CHAR()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

Passing too many arguments also results in an error:

SELECT TO_CHAR(DATE '2035-09-26', 'yy', 'NLS_DATE_LANGUAGE = spanish', 'oops!' )
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TO_CHAR(DATE '2035-09-26', 'yy', 'NLS_DATE_LANGUAGE = spanish', 'oops!' )
FROM DUAL
Error at Command Line : 1 Column : 72
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: