Oracle TO_CHAR

The Oracle TO_CHAR() function converts a DATE or INTERVAL value to a string in a specified date format.

The Oracle TO_CHAR() function is very useful for formatting the internal date data returned by a query in a specific date format.

Syntax

The following illustrates the syntax of the TO_CHAR() function:

TO_CHAR(expr [, date_format] [, nslparam]);
Code language: SQL (Structured Query Language) (sql)

Arguments

The Oracle TO_CHAR() accepts three arguments:

1) expr

The expr is a DATE or an INTERVAL value that should be converted.

The data type of expr can be  DATE,  TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE.

2) date_format

The date_format is a string that determines the format that the result string should be in.

The date_format argument is optional. If you omit it, the TO_CHAR() function will use the default date format for DATE values, default timestamp format for TIMESTAMP and TIMESTAMP WITH TIME ZONE value, and default timestamp with time zone format for TIMESTAMP WITH TIME ZONE values.

To compose value for the date_format argument, you use the Oracle date format model.

3) nlsparam

The nlsparam argument specifies the languages for names and abbreviations of day and month e.g., Monday, Mon, January, Jan, etc., in the result string.

The nlsparam argument has the following form:

'NLS_DATE_LANGUAGE = language'
Code language: SQL (Structured Query Language) (sql)

This nlsparam argument is also optional. If you omit it, the TO_CHAR() function uses the default date language.

Return value

The Oracle TO_CHAR() function returns a string represented a DATE or INTERVAL value in a specified format.

Examples

A) Convert the current system date

The following statement converts the current system date to a string with the format YYYY-MM-DD:

SELECT
  TO_CHAR( sysdate, 'YYYY-MM-DD' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

The result is:

2017-08-02Code language: SQL (Structured Query Language) (sql)

To convert the current system date to a long date string, you use the DL date format as follows:

SELECT
  TO_CHAR( sysdate, 'DL' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Wednesday, August 02, 2017
Code language: SQL (Structured Query Language) (sql)

To display the names of days and months in another language e.g., French, you use the nlsparam argument as follows:

SELECT
  TO_CHAR( sysdate, 
                  'DL' , 
                  'NLS_DATE_LANGUAGE = FRENCH')
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

The result is in French:

Mercredi, Août 02, 2017 
Code language: SQL (Structured Query Language) (sql)

B)  Format an interval example

This example uses the TO_CHAR() function to format an interval:

SELECT 
   TO_CHAR(INTERVAL '600' SECOND, 'HH24:MM') result
FROM 
   DUAL;Code language: SQL (Structured Query Language) (sql)

Here is the output:

RESULT            
-------------------
+00 00:10:00.000000

C) Format dates from table data example

See the employees table in the sample database:

The following statement uses the TO_CHAR() function to return the quarter when the employees joined the company in 2016:

SELECT
  first_name, 
  last_name, 
  TO_CHAR( hire_date, 'Q' ) joined_quarter
FROM
  employees
WHERE
  hire_date BETWEEN DATE  '2016-01-01'  AND date '2016-12-31'
ORDER BY
 first_name, last_name;
Code language: SQL (Structured Query Language) (sql)

The picture below illustrates the result:

In this tutorial, you have learned how to use the OracleTO_CHAR() function to convert a DATE or INTERVAL value to a string in a specified format.