Date and Time formats

When a date format is used by TO_CHAR or TO_DATE they return a formatted date/time. When used by TRUNC they will return the first day of the period. When used by ROUND the values will round up at mid year/mid month (July 1 or 16th day)

 CC    Century
 SCC   Century BC prefixed with -

 YYYY  Year 1956
 SYYY  Year BC prefixed with -
 IYYY  ISO Year 1956
 YY    Year 56
 RR    Year 56 rollover for Y2K compatibility *
 RRRR  Year rollover (accepts 2 digits, returns 4) *
 YEAR  Year spelled out
 SYEAR Year spelled out BC prefixed with -
 BC    BC/AD Indicator *

 Q     Quarter : Jan-Mar=1, Apr-Jun=2

 MM    Month of year 01, 02…12
 MON   JAN, FEB
 MONTH In full [January  ]…[December ]
 FMMONTH In full [January]…[December] no trailing spaces
 RM    Roman Month I, II…XII *

 WW    Week of year 1-52
 W     Week of month 1-5
 IW    ISO std week of year

 DDD   Day of year 1-366 *
 DD    Day of month 1-31
 D     Day of week 1-7
 DAY   In full [Monday   ]…[Sunday   ]
 FMDAY In full [Monday]…[Sunday] no trailing spaces
 DY    MON…SUN
 DDTH  Ordinal Day 7TH
 DDSPTH Spell out ordinal SEVENTH
 J     Julian Day (days since 31/12/4713)

 HH    Hours of day (1-12)
 HH12  Hours of day (1-12)
 HH24  Hours of day (1-24)
 AM    am or pm *
 PM    am or pm *
 A.M.  a.m. or p.m. * 
 P.M.  a.m. or p.m. *
 MI    Minutes 0-59
 SS    Seconds 0-59 *
 SSSSS Seconds past midnight (0-86399) *

 TH    Convert to ordinal format. e.g. 1 becomes 1st
 SP    Spelled format (English.) Add SP to the end of a number element. 
       For example MMSP for months or HHsp for hours. 

 TZD   Abbreviated time zone name. ie PST.
 TZH   Time zone hour displacement
 TZM   Time zone minute displacement
 TZR   Time zone region

• The following punctuation -/,.;: can be included in any date format, any other chars can be included "in quotes"

• Formats marked with * can only be used with TO_CHAR or TO_DATE not TRUNC() or ROUND()

• Formats that start with FM - zeros and blanks are suppressed.
e.g.
to_char(sysdate, 'FMMonth DD, YYYY'); will return 'June 9, 2005' not 'June 09 2005'

• Date formats that are spelled out in characters will adopt the capitalisation of the format
e.g.
'MONTH' =JANUARY
'Month' = January

Examples

SQL> Select to_char(sysdate, 'yyyy/mm/dd') "Date Today" FROM dual;
 '2010/12/24'

SQL> Select to_char(sysdate, 'FMMonth DD, YYYY') FROM dual;
 'June 9, 2005'

SQL> select to_char(sysdate,'HH24:MI:SS') "Time Now" from dual;
 '14:35:56'

Related

Number formats
TO_CHAR - Convert to character string
TO_DATE - Convert to date value
Oracle Dates - SQL for Web Nerds by Philip Greenspun