Press "Enter" to skip to content

Oracle TO_CHAR Date Format Examples

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.

TO_CHAR(date) Syntax

TO_CHAR(date, format, [nls_param])

Date Format Specifiers

Format ModelDescription
CC, SCCCentury (S prefixes BC dates with a minus sign)
YYYY, SYYYYYear (S prefixes BC dates with a minus sign)
IYYYYear based on ISO standard
YYY, YY, YLast three, two or one digits of the year
IYY, IY, ILast 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, SYEARYear spelled out (S prefixes BC dates with a minus sign)
RRLast two digits of year in another century (allows for year 2000)
BC, ADBC or AD indicator
B.C., A.D.BC or AD indicators with periods
QNumeric quarter of the year (1-4 with Jan-Mar=1)
MM2 number month (Jan = 01)
RMRoman numeral month
MONTHName of month spelled out (upper case - month is upper case)
MONabbreviated name of month (upper case - month is upper case)
WWWeek of the year (1-53)
IWWeek of the year (1-52, 1-53) based on ISO standard
WWeek of month (1-5)
DDDday of year (1-366) (Don't forget leap years)
DDday of month (1-31)
Dday of week (1-7)
DAYName of day (upper case, day is upper case)
DYAbbreviated name of day
JJulian day (Number of days since January 1, 4712 BC)
AM,PMMeridian indicator
A.M., P.M.Meridian indicator with periods.
HH, HH12Hour of day (0-12)
HH24Use 24 hour clock for hours (1-24)
MIMinute (0-59)
SSSecond (0-60)
SSSSS(five S's) seconds past midnight. (0-86399)
NoneDate must be in the format 'DD-MON-YY';

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

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *