|
When a date format is used by TO_CHAR or TO_DATE
they return part of the 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 2001 SYYY Year BC prefixed with - IYYY ISO Year 2001 YY Year 01 RR Year 01 rollover for Y2K compatibility * 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 RM Roman Month I, II...XII * MONTH In full [January ]...[December ] FMMONTH In full [January]...[December] MON JAN, FEB 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. * MI Minutes 0-59 SS Seconds 0-59 * SSSS Seconds past midnight (0-86399) * 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()
Date formats that are spelled out in characters will adopt the capitalisation
of the format
e.g.
'MONTH' =JANUARY
'Month' = January
Links
Oracle
Dates - SQL for Web Nerds by Philip Greenspun