This will be used to extract various date formats.
The available date formats as follows.
Syntax: to_char (date, format)
DATE FORMATS
D -- No of days in week
DD -- No of days in month
DDD -- No of days in year
MM -- No of month
MON -- Three letter abbreviation of month
MONTH -- Fully
spelled out month
RM -- Roman numeral month
DY -- Three letter abbreviated day
DAY -- Fully spelled out day
Y -- Last one digit of the year
YY -- Last two digits of the year
YYY -- Last three digits of the year
YYYY -- Full four digit year
SYYYY
-- Signed year
I -- One digit year from ISO standard
IY -- Two digit year from ISO standard
IYY -- Three digit year from ISO standard
IYYY -- Four digit year from ISO standard
Y, YYY -- Year with comma
YEAR -- Fully spelled out year
CC -- Century
Q -- No of quarters
W -- No of weeks in month
WW -- No of weeks in year
IW -- No of weeks in year from ISO standard
HH -- Hours
MI -- Minutes
SS -- Seconds
FF -- Fractional seconds
AM or PM -- Displays
AM or PM depending upon time of day
A.M or P.M -- Displays
A.M or P.M depending upon time of day
AD or BC -- Displays
AD or BC depending upon the date
A.D or B.C -- Displays
AD or BC depending upon the date
FM -- Prefix to month or day, suppresses padding of month or day
TH -- Suffix to a number
SP -- suffix to a number to be spelled out
SPTH -- Suffix combination of TH and SP to be both spelled out
THSP -- same as SPTH
Ex:
SQL> select
to_char(sysdate,'dd month yyyy hh:mi:ss am dy') from dual;
TO_CHAR(SYSDATE,'DD
MONTH YYYYHH:MI
----------------------------------------------------
24
december 2006 02:03:23 pm sun
SQL> select
to_char(sysdate,'dd month year') from dual;
TO_CHAR(SYSDATE,'DDMONTHYEAR')
-------------------------------------------------------
24
december two thousand six
SQL> select to_char(sysdate,'dd fmmonth year') from dual;
TO_CHAR(SYSDATE,'DD
FMMONTH YEAR')
-------------------------------------------------------
24 december
two thousand six
SQL> select to_char(sysdate,'ddth DDTH') from dual;
TO_CHAR(S
------------
24th 24TH
SQL> select to_char(sysdate,'ddspth DDSPTH') from dual;
TO_CHAR(SYSDATE,'DDSPTHDDSPTH
------------------------------------------
twenty-fourth
TWENTY-FOURTH
SQL> select to_char(sysdate,'ddsp Ddsp DDSP ') from dual;
TO_CHAR(SYSDATE,'DDSPDDSPDDSP')
------------------------------------------------
twenty-four
Twenty-Four TWENTY-FOUR
