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] 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] 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) SPHH Spell out SEVEN AM am or pm * PM am or pm * A.M. a.m. or p.m. * P.M. a.m. or p.m. * TH Convert to ordinal format. e.g. 1 becomes 1st MI Minutes 0-59 SS Seconds 0-59 * SSSSS Seconds past midnight (0-86399) * 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
