TO_CHAR converts the date of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format. This post explains Oracle To_Char date format examples with syntax and date format specifiers.

Advertisement

TO_CHAR(date) Syntax

TO_CHAR(date, format, [nls_param])

Date Format Specifiers

Format Model Description
CC, SCC Century (S prefixes BC dates with a minus sign)
YYYY, SYYYY Year (S prefixes BC dates with a minus sign)
IYYY Year based on ISO standard
YYY, YY, Y Last three, two or one digits of the year
IYY, IY, I Last three, two or one digits of the ISO year
Y,YYY (Four Y's with comma) put a comma in the year (1,995)
YEAR, SYEAR Year spelled out (S prefixes BC dates with a minus sign)
RR Last two digits of year in another century (allows for year 2000)
BC, AD BC or AD indicator
B.C., A.D. BC or AD indicators with periods
Q Numeric quarter of the year (1-4 with Jan-Mar=1)
MM 2 number month (Jan = 01)
RM Roman numeral month
MONTH Name of month spelled out (upper case - month is upper case)
MON abbreviated name of month (upper case - month is upper case)
WW Week of the year (1-53)
IW Week of the year (1-52, 1-53) based on ISO standard
W Week of month (1-5)
DDD day of year (1-366) (Don't forget leap years)
DD day of month (1-31)
D day of week (1-7)
DAY Name of day (upper case, day is upper case)
DY Abbreviated name of day
J Julian day (Number of days since January 1, 4712 BC)
AM,PM Meridian indicator
A.M., P.M. Meridian indicator with periods.
HH, HH12 Hour of day (0-12)
HH24 Use 24 hour clock for hours (1-24)
MI Minute (0-59)
SS Second (0-60)
SSSSS (five S's) seconds past midnight. (0-86399)
None Date must be in the format 'DD-MON-YY';
Related:  Oracle Database: Convert Dates to Before 2000 for Years 50-99 and to 2000 and Later for Years 00-49

Oracle TO_CHAR Date Format Examples

1. Change date to DD/MM/YYYY format.

SELECT TO_CHAR (SYSDATE, 'DD/MM/YYYY') FROM DUAL;

Output

20/07/2018

2. Change date to DD/Month/YYYY with 24hr time format.

SELECT TO_CHAR (SYSDATE, 'DD/FMMonth/YYYY HH24:MI:SS') FROM DUAL;

Note: Above using FMMonth to suppress blank. If you will use the only Month then the output will be as below:

20/July /2018

Output

20/July/2018 11:36:30

3. Change date to just YYYYMM format.

SELECT TO_CHAR (SYSDATE, 'YYYYMM') FROM DUAL;

Output

201807

4. Get the day of the year, the month, and the week for the date.

SELECT TO_CHAR (SYSDATE, 'fmDDD DD D ') FROM DUAL;

Output

201 20 6

5. Get some detailed formatting for reporting purposes.

SELECT TO_CHAR (SYSDATE, '"In month "RM" of year "YEAR') FROM DUAL;

Output

In month VII of year TWENTY EIGHTEEN

See also:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.