Oracle Date Format

Oracle DATE datatype is used to store point-in-time values related to date and time. Oracle date datatype is stored in fixed length field of 7 bytes allows the storing of the year in 4 digit format, month, day, hours, minutes and seconds. Oracle dates are stored in julian era starting from January 1, 4712 BC through December 31, 4172 AD. Dates are defaulted to AD unless the format mask BC is specifically used

When displaying a DATE, Oracle converts the value from the internal format to a printable string. The conversion is done using a TO_CHAR function. Default date format returned by oracle is always in 'DD-MON-YY' unless specifically set by the database administrator in the init.ora

select birthdate from person;
14-JUN-01

select to_char(birthdate, 'DD-MON-YYYY') from person;
14-JUN-2001

select to_char(birthdate, 'DD-MON-YYYY HH24:MI:SS') from person;
14-JUN-2001 14:22:27

Oracle date formats used in the to_char function are specified as below.

Oracle Date Format
CC Century
YYYY 4 digit Year [ 2001 ]
YY Year in 2 digit format [ 01 ]
RR Year in 2 digit format for rollover/y2k compatibility [ 01 ]
YEAR Year fully spelled out [ Two Thousand and Eight ]
Q Quarter Number [ Jan-Mar = 1, Oct-Dec = 4 ]
MM Month of year [ 01, 02 ]
RM Roman Month [ I, II ]
MONTH Month fully spelled out [January]
MON Month abbreviation [ JAN ]
WW Week of year [ 1 to 52 ]
W Week of month [ 1 to 5 ]
DDD Day of year [ 1-366 ]
DD Day of month [ 1-31 ]
D Day of week [ 1-7 ]
DAY Day fully spelled out [ Monday ]
DY Day abbreviation [ MON ]
DDTH Ordinal Day [ 6TH ]
DDSPTH Fully spelled out ordinal [ SIXTH ]
HH Hour of day [ 12 Hour format ]
HH12 Hour of day [ 12 Hour format ]
HH24 Hour of day[ 24 Hour format ]
SPHH Hour fully spelled out [ SIX ]
AM am or pm
PM am or pm
MI Minutes 0-59
SS Seconds 0-59
SSSSS Seconds since midnight (0-86399)