TO_CHAR is an overloading function which converts or extracts various types of input values into desired or readable output.
TO_CHAR for Datetime
There're several ways to use TO_CHAR to operate datetime columns.
In most cases, we use TO_CHAR function to format or extract datetime values to desired display.
Format Datetime
In order to well-format a datetime value, we should know datetime format elements.
DATE Column
For example, we format a DATE column like the followings.
SQL> set heading off;
SQL> select TO_CHAR(sysdate, 'Month dd, YYYY, HH:MI A.M.') from dual;
April 19, 2023, 02:01 A.M.
TIMESTAMP Column
For TIMESTAMP columns, we can additionally extract time zone information.
SQL> select TO_CHAR(systimestamp, 'Month dd, YYYY, HH:MI A.M. TZR') from dual;
April 19, 2023, 02:04 A.M. -04:00
In the above example, we derive the timestamp from SYSTIMESTAMP, an Oracle built-in function for getting the current timestamp.
Since DATE columns do not contain any time zone information, it throws error ORA-01821 if you specify any time zone element in the format string.
NLS Translation
For some reason, you may translate the display format from one to another. For example, we'd like to display it in a multi-byte language, say JAPANESE, so we set NLS_DATE_LANGUAGE in the third argument.
SQL> select TO_CHAR(sysdate, 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE=JAPANESE') from dual;
4月 19, 2023, 11:42 午後
The NLS format in the third argument is a one-time setting, it does not affect your session or other statements.
Multi-word NLS_LANGUAGE
To make a multi-word NLS_LANGUAGE be used in the argument, it must be quoted.
SQL> select TO_CHAR(sysdate, 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE=''LATIN AMERICAN SPANISH''') from dual;
Abril 19, 2023, 11:48 P.M.
Using 2 consecutive single quotes in a quoted string to represent a single quote plays the trick.
To see all valid NLS_LANGUAGE codes in Oracle, you may follow the link and have a look.
Extract Datetime
We can extract any combination of datetime column to meet desired business logic.
For example, we'd like to know which month has the highest hiring record.
SQL> set heading on pagesize 1000;
SQL> column hire_month format a10;
SQL> column number_of_month format 99;
SQL> select TO_CHAR(hire_date, 'Month') hire_month, count(*) number_of_employees from hr.employees group by TO_CHAR(hire_date, 'Month') order by 2 desc;
HIRE_MONTH NUMBER_OF_EMPLOYEES
---------- -------------------
March 17
January 14
February 13
June 11
August 9
December 7
April 7
July 7
May 6
October 6
September 5
November 5
12 rows selected.
As you can see "March" is the most hiring month of all years, according to the data.
Although you can also use EXTRACT function to do the similar work, TO_CHAR is still more controllable and versatile.
TO_CHAR for Number
There're several ways to use TO_CHAR to operate number columns.
Format Number
The set of number format elements is usually used to format financial data including currency sign, grouping separator, presentation for negative values, etc.
Here we format the salary column for every employee in the company.
SQL> select employee_id, TO_CHAR(salary, 'L999G999D99') salary from hr.employees;
EMPLOYEE_ID SALARY
----------- ---------------------
100 $24,000.00
101 $17,000.00
102 $17,000.00
103 $9,000.00
104 $6,000.00
...
Through the report, we can understand their salaries in human-readable format.
NLS Settings
To control the format more finely, we can set NLS settings.
NLS_CURRENCY
For example, we'd like to put a whitespace between the currency symbol and the number, we can set NLS_CURRENCY like this.
SQL> select employee_id, TO_CHAR(salary, 'L999G999D99', 'NLS_CURRENCY=''$ ''') salary from hr.employees;
EMPLOYEE_ID SALARY
----------- ---------------------
100 $ 24,000.00
101 $ 17,000.00
102 $ 17,000.00
103 $ 9,000.00
104 $ 6,000.00
...
Please note that, the NLS format in the third argument is a one-time setting, it does not affect your session or other statements.
NLS_NUMERIC_CHARACTERS
Additionally, we'd like to use a whitespace ( ) to be the group separator, furthermore, use a comma (,) to be the decimal character. We should use NLS_NUMERIC_CHARACTERS to change them.
SQL> select employee_id, TO_CHAR(salary, 'L999G999D99', 'NLS_CURRENCY=''$ '' NLS_NUMERIC_CHARACTERS='', ''') salary from hr.employees;
EMPLOYEE_ID SALARY
----------- ---------------------
100 $ 24 000,00
101 $ 17 000,00
102 $ 17 000,00
103 $ 9 000,00
104 $ 6 000,00
...
Multiple NLS parameters can be set and separated by whitespaces or newlines.