2017  Kodetalk | Feedback | Privacy Policy | Terms | About

date formats in oracle


In my work daily i need to work with date formats. But i don"t know much conversion functions with dates.

Can you please provide a needful info on that.


In our daily life we will use so many conversion functions.

Below are some example.

 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
 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
 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.
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
'Month' = January


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

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;

Answer is