Oracle TO_CHAR Function With Example

Oracle TO_CHAR Function With Example | In Oracle the to_char function is used to convert either number or date into the required format. The to_char(number) is given to convert the number to a given format, and to_char(date) is given to convert the date value to the required format. Here, we will discuss to_char(date) function.

The Oracle SQL to_char function is used to convert oracle DATE or INTERVAL into VARCHAR2 datatype in a specified date format. It is very useful for formatting the internal date returned by a query in a specific date format.

Syntax for to_char() function:-
TO_CHAR(expression [, 'date_format' ] [, nslparam ] )

The expression can be a DATE or INTERVAL which should be converted. The data type of expression can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE.

The date_format is an optional parameter and it determines the format of the result string. If we don’t specify date_format then it will use Oracle default format.

The nlsparam argument is also an optional argument and it specifics the languages for names and abbreviations of day and month in the result string. If we don’t specify it then it uses the default date language.

'NLS_DATE_LANGUAGE = language'

Example:-

SQL> SELECT sysdate FROM dual;

SYSDATE
---------
24-AUG-20

This is the Oracle default date format. Using to_char we can convert it into different string date format.

SQL> SELECT TO_CHAR(sysdate, 'DD/MM/YYYY')
     FROM dual;

TO_CHAR(SY
----------
24/08/2020

SQL> SELECT TO_CHAR(sysdate, 'DAY') FROM dual;

TO_CHAR(S
---------
MONDAY

SELECT TO_CHAR( sysdate, 'DL') FROM dual;

TO_CHAR(SYSDATE,'DL')
-----------------------------
Monday, August 24, 2020

TO display the string in another language we can use another language in nslparam argument. Example:-

SQL> SELECT 
     TO_CHAR(sysdate, 'DL', 'NLS_DATE_LANGUAGE = FRENCH')
     FROM dual;

TO_CHAR(SYSDATE,'DL','NLS_DA
----------------------------
Lundi, Ao√t 24, 2020

Example using interval,

SQL> SELECT 
     TO_CHAR(INTERVAL '600' SECOND, 'HH24:MM')
     FROM dual;

TO_CHAR(INTERVAL'60
-------------------
+00 00:10:00.000000

Different Foramts used in to_char() function
The formats can be categories into these types,
1) Format’s returning character value
2) Format’s returning number value
3) Format’s with a prefix

Oracle TO_CHAR Function Date Format

Below formats returns characters/String and these are case-sensitive.

Format	Return value	Example
DAY	Day in full forms.	SUNDAY, MONDAY e.t.c.
DY	Day in 3 characters short form.	SUN, MON, FRI e.t.c.
MONTH	Month in full form.	SEPTEMBER, NOVEMBER, e.t.c
MON	Month in 3 characters short form.	SEP, OCT, NOV e.t.c
YEAR	Year in characters.	TWENTY NINETEEN
DS	Short date format	8/24/2020
PM P.M.	Meridian indicator with or without periods	A.M.
TS	Short time format	6:51:59 AM

SQL> SELECT sysdate FROM dual;

SYSDATE
---------
12-AUG-20

SQL> SELECT TO_CHAR(sysdate, 'DAY') FROM dual;

TO_CHAR(S
---------
WEDNESDAY

Similarly,

• TO_CHAR( sysdate , 'DY') => WED
• TO_CHAR( sysdate , 'MONTH') => AUGUST
• TO_CHAR( sysdate , 'MON') => AUG
• TO_CHAR( sysdate , 'YEAR') => TWENTY TWENTY
• TO_CHAR(sysdate , 'TS') => 10:43:33 AM

Note that these formats are case-sensitive.

• TO_CHAR( sysdate , 'day') => wednesday
• TO_CHAR( sysdate , 'dy') => wed
• TO_CHAR( sysdate , 'month') => august
• TO_CHAR( sysdate , 'mon') => aug
• TO_CHAR( sysdate , 'year') => twenty twenty

Oracle to_char Function Number Format

The below formats return number.

Formats	Return Value
D	It gives the day of the week (1-7). In Oracle, the day start from sunday, so sunday(1), monday(2), tuesday(3) e.t.c.
DD	Day of the month (1-31)
DDD	Day of the year (1-366)
MM	Month of the year
RM	Roman numberal month (I-XII; JAN = I)
W	Week of the month (1-5)
WW	Week of year (1-53)
IW	Week of year (1-53) based on ISO standard
Q	Quarter of the year (1, 2, 3, 4; JAN-MAR=1)
YYYY	Year in 4-digit
Y YY YYY	Last 1, 2, OR 3 digit(s) of the year
IYYY	Year in 4-digit based on the ISO standard
I IY IYY	Last 1, 2, OR 3 digit(s) of the year based on ISO standard
J	Julain day, the number of days since 1 JAN 4712 BC.
HH	Hours in 12 hour format (1-12)
HH24	Hours in 24 hour format (1-24)
MI	Minute (0-59)
SS	Second (0-59)
SSSS	Seconds past midnight (0-86399)
FF	Fractional seconds

Examples:-

SQL> SELECT TO_CHAR(sysdate) FROM dual;

TO_CHAR(S
---------
12-AUG-20

SQL> SELECT TO_CHAR(sysdate, 'D') FROM dual;

T
-
4

Similarly,

TO_CHAR(SYSDATE, 'DD') => 12
TO_CHAR(SYSDATE, 'DDD') => 225
TO_CHAR(SYSDATE, 'MM') => 08
TO_CHAR(SYSDATE, 'YY') => 20
TO_CHAR(SYSDATE, 'YYYY') => 2020
TO_CHAR(SYSDATE, 'HH:MI:SS') => 03:31:31
TO_CHAR(SYSDATE, 'HH24:MI:SS') => 15:31:38

Format for Prefix

The TH and SPTH formats are used as prefix format. Here SP represents spell out. These format can’t be used alone. It always used along with previous disscussed formats.

DDTH:- day of the month with prefix TH
DTH, DDDTH, MMTH, YYTH, YYYYTH

DDSPTH:- day of the month with prefix in character
DSPTH, YYYYSPTH, and e.t.c.

Examples:-

SQL> SELECT sysdate FROM dual;

SYSDATE
---------
12-AUG-20

SQL> SELECT TO_CHAR(sysdate, 'DD') FROM dual;

TO
--
12

SQL> SELECT TO_CHAR(sysdate, 'DDTH') FROM dual;

TO_C
----
12TH

SQL> SELECT TO_CHAR(sysdate, 'DDSPTH') FROM dual;

TO_CHAR(SYSDAT
--------------
TWELFTH

Similarly,

• TO_CHAR(sysdate, 'D') => 4
• TO_CHAR(sysdate, 'DTH') => 4TH
• TO_CHAR(sysdate, 'DSPTH') => FOURTH
• TO_CHAR(sysdate, 'DDDTH') => 225TH
• TO_CHAR(sysdate, 'DDDSPTH') => TWO HUNDRED TWENTY-FIFTH
• TO_CHAR(sysdate, 'MMTH') => 08TH
• TO_CHAR(sysdate, 'MMSPTH') => EIGHTH
• TO_CHAR(sysdate, 'YYTH') => 20TH
• TO_CHAR(sysdate, 'YYSPTH') => TWENTIETH
• TO_CHAR(sysdate, 'YYYYTH') => 2020TH
• TO_CHAR(sysdate, 'YYYYSPTH') => TWO THOUSAND TWENTIETH

Example of to_char function With to_date

Example1:- Display 15-JUN-2025 into 15/JUNE/25 format.

SQL> SELECT 
     TO_CHAR('15-JUN-2025', 'DD/MONTH/YY')
     FROM dual;

SELECT TO_CHAR('15-JUN-2025', 'DD/MONTH/YY') FROM dual
               *
ERROR at line 1:
ORA-01722: invalid number

Whenever we are using to_char() then always first parameter must be in Oracle date data type otherwise Oracle server returns an error. For this we have to use to_date() function. Learn more about to_date function in Oracle database.

SQL> SELECT 
     TO_CHAR(TO_DATE('15-JUN-2025'), 'DD/MONTH/YY')
     FROM dual;

TO_CHAR(TO_DATE
---------------
15/JUNE     /25

Fill Mode (FM) Used with to_char Format

In Oracle whenever we are using to_char() function with MONTH, DAY format then oracle server internally automatically allocates maximum 9 bytes of memory from the month/day field. Whenever passed month/day having less than 9 bytes then the oracle server returns blank spaces in place of remaining bytes. To overcome this problem oracle provided FM(fill mode) format element within to_char() function. This FM format element not only filling gaps but also automatically suppressed leading zero’s (first zero).

SQL> SELECT 
     TO_CHAR(TO_DATE('15-JUN-25'), 'DD/FMMONTH/YY')
     FROM dual;

TO_CHAR(TO_DATE
---------------
15/JUNE/25

Another Example, without using fill mode,

SQL> SELECT 
     TO_CHAR(TO_DATE('06-JUN-25'), 'DAY/MONTH/YY') 
     FROM dual;

TO_CHAR(TO_DATE('06-JU
----------------------
FRIDAY   /JUNE     /25

Using filling mode for both day and month,

SQL> SELECT 
     TO_CHAR(TO_DATE('06-JUN-25'), 'FMDAY/FMMONTH/YY')
     FROM dual;

TO_CHAR(TO_DATE('06-JU
----------------------
FRIDAY/JUNE     /25

Fill mode using only for day,

SQL> SELECT TO_CHAR(TO_DATE('06-JUN-25'), 'FMDAY/MONTH/YY')
     FROM dual;

TO_CHAR(TO_DATE('06-JU
----------------------
FRIDAY/JUNE/25

Using fill mode only for month,

SQL> SELECT TO_CHAR(TO_DATE('06-JUN-25'), 'DAY/FMMONTH/YY')
     FROM dual;

TO_CHAR(TO_DATE('06-JU
----------------------
FRIDAY   /JUNE/25

Examples Using Tables

We are using emp table to demenstrate these examples.

SQL> SELECT ename, hiredate FROM emp;

ENAME      HIREDATE
---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81
WARD       22-FEB-81
JONES      02-APR-81
MARTIN     28-SEP-81
BLAKE      01-MAY-81
CLARK      09-JUN-81
SCOTT      19-APR-87
KING       17-NOV-81
TURNER     08-SEP-81
ADAMS      23-MAY-87
JAMES      03-DEC-81
FORD       03-DEC-81
MILLER     23-JAN-82

14 rows selected.

Example1:- Write a query to display ename, and only year of hiring from hiredate from emp table.

SQL> SELECT ename, TO_CHAR(hiredate, 'YYYY') 
     FROM emp;

ENAME      TO_C
---------- ----
SMITH      1980
ALLEN      1981
WARD       1981
...........
14 rows selected.

Example2:- Write a query to display the employees who are joing in the month december from the emp table by using to_char function.

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'MON') = 'dec';

or we can also use,

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'MM') = '12';

We can also use ‘12’ as a number,

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'MM') = 12;

Example3:- Write a query to display the employee who are joining in the month DECEMBER by using to_char() and MONTH format.

SQL> SELECT * FROM emp WHERE
     TO_CHAR(hiredate,'MONTH')='DECEMBER';

no rows selected.

Solution:- use FM i.e. fill mode

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'FMMONTH')='DECEMBER';

Example4:- Write a query to display the employees who are joing in the month february from emp table by using to_char() and MM format.

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate,'MM') = '2';

no rows selected.

Solution,

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'FMMM') = '2';

Other solutions are,

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate,'MM') = '02';

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'MON') = 'FEB';

Example5:- Write a query to display the employees who are joining in the year 81 from the emp table using to_char function.

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'YY')='81';

Example6:- Write a query to display the employee who are joing before 15 of every month from emp table by using to_char() function.

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'DD') < 15;

Or, 

SQL> SELECT * FROM emp 
     WHERE TO_CHAR(hiredate, 'DD') < '15';

Example7:- Write a query to return the quarter when the employee joined the company from emp table.

SQL> SELECT
    ename, TO_CHAR(hiredate, 'Q')
    FROM emp;

ENAME      T
---------- -
SMITH      4
ALLEN      1
WARD       1
JONES      2
MARTIN     3
BLAKE      2
……..
……..

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• How to unlock Scott user in SQL Plus
• Error ORA 01031 insufficient privileges

Search