Here you will find list of Oracle Date Format elements:
| Format element | Description |
|---|---|
| AD A.D. |
AD indicator. |
| AM A.M. PM P.M. |
Meridian indicator. |
| BC B.C. |
BC indicator. |
| CC | Century |
| SCC | Century BC prefixed with – |
| D | Day of week: 1-7. |
| DAY | Name of day. |
| DD | Day of month: 1-31. |
| DDD | Day of year: 1-366. |
| DL | Date in long date format. |
| DS | Date in short date format. |
| DY | Abbreviated name of day. |
| E | Abbreviated era name. |
| EE | Full era name. |
| FF[1..9] | Fractional seconds. Example: Format ‘MI.SS.FF2’ |
| FM | Returns a value without blanks (leading and trailing). |
| FX | Format exact. |
| HH | Hour of day: 1-12. |
| HH24 | Hour of day: 0-23. |
| IW | Week of year: 1-53 by ISO standard. |
| I IY IYY IYYYY |
Last 1, 2, 3 or 4 digits of year by ISO standard. |
| J | Number of days since January 1, 4712 BC – Julian day. |
| MI | Number of minutes: 1-59. |
| SS | Number of seconds: 1-59. |
| SSSSS | Number of seconds past midnight: 0-86399. |
| MM | Month: 01-12. |
| MON | Abbreviated name of month. |
| MONTH | Month name. |
| Q | Quarter of year: 1-4. |
| RM | Roman numeral month: I-XII. |
| RR RRRR |
Century dates using 2 or 4 digits. |
| TS | Returns a short time format. |
| TZD | Daylight savings information |
| TZH | Time zone: hour. |
| TZM | Time zone: minute. |
| TZR | Time zone: region information. |
| W WW |
Week of month: 1-5. Week of year: 1-53. |
| X | Local radix char. |
| YEAR | Year in words, like: TWENTY ELEVEN |
| Y YY YYY YYYY |
Last 1, 2, 3 or 4 digits of year. |
Examples below are Oracle conversions from date to char using date format elements:
SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY') FROM dual --result: '23.07.2011' SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') FROM dual --result: '23.07.2011 15:43:46' SELECT TO_CHAR(SYSDATE, 'DAY MONTH YYYY Q') FROM dual --result: 'SATURDAY JULY 2011 3' SELECT TO_CHAR(SYSDATE, 'FMDAY MONTH YYYY Q') FROM dual --result: 'SATURDAY JULY 2011 3' SELECT TO_CHAR(SYSDATE, 'MON DDth, YYYY') FROM dual --result:'JUL 23RD, 2011'
Examples below are Oracle conversions from char to date using date format elements:
SELECT TO_DATE('23.07.2011', 'DD.MM.YYYY')
FROM dual
--result: 23.07.2011
SELECT TO_DATE('23.07.2011 15:43:46', 'DD.MM.YYYY HH24:MI:SS')
FROM dual
--result: 23.07.2011 15:43:46
SELECT TO_DATE('18 JULY 2011', 'DD MONTH YYYY')
FROM dual
--result: 18.7.2011