參考:http://www.oradev.com/oracle_date_format.jsp
Oracle date format
With the functions to_char and to_date, a date format can be used. Example:select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
will return something like: 24/03/2006 14:36:43
Here is a list of all the formats that can be used:
| Format mask | Description |
|---|---|
| CC | Century |
| SCC | Century BC prefixed with - |
| YYYY | Year with 4 numbers |
| SYYY | Year BC prefixed with - |
| IYYY | ISO Year with 4 numbers |
| YY | Year with 2 numbers |
| RR | Year with 2 numbers with Y2k compatibility |
| YEAR | Year in characters |
| SYEAR | Year in characters, BC prefixed with - |
| BC | BC/AD Indicator * |
| Q | Quarter in numbers (1,2,3,4) |
| MM | Month of year 01, 02...12 |
| MONTH | Month in characters (i.e. January) |
| MON | JAN, FEB |
| WW | Weeknumber (i.e. 1) |
| W | Weeknumber of the month (i.e. 5) |
| IW | Weeknumber of the year in ISO standard. |
| DDD | Day of year in numbers (i.e. 365) |
| DD | Day of the month in numbers (i.e. 28) |
| D | Day of week in numbers(i.e. 7) |
| DAY | Day of the week in characters (i.e. Monday) |
| FMDAY | Day of the week in characters (i.e. Monday) |
| DY | Day of the week in short character description (i.e. SUN) |
| J | Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle) |
| HH | Hournumber of the day (1-12) |
| HH12 | Hournumber of the day (1-12) |
| HH24 | Hournumber of the day with 24Hours notation (0-23) |
| AM | AM or PM |
| PM | AM or PM |
| MI | Number of minutes (i.e. 59) |
| SS | Number of seconds (i.e. 59) |
| SSSSS | Number of seconds this day. |
| DS | Short date format. Depends on NLS-settings. Use only with timestamp. |
| DL | Long date format. Depends on NLS-settings. Use only with timestamp. |
| E | Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only) |
| EE | The full era name |
| FF | The fractional seconds. Use with timestamp. |
| FF1..FF9 | The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds. |
| FM | Fill Mode: suppresses blianks in output from conversion |
| FX | Format Exact: requires exact pattern matching between data and format model. |
| IYY or IY or I | the last 3,2,1 digits of the ISO standard year. Output only |
| RM | The Roman numeral representation of the month (I .. XII) |
| RR | The last 2 digits of the year. |
| RRRR | The last 2 digits of the year when used for output. Accepts fout-digit years when used for input. |
| SCC | Century. BC dates are prefixed with a minus. |
| CC | Century |
| SP | Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten" |
| SPTH | Spelled and ordinal format; 1 results in first. |
| TH | Converts a number to it's ordinal format. For example 1 becoms 1st. |
| TS | Short time format. Depends on NLS-settings. Use only with timestamp. |
| TZD | Abbreviated time zone name. ie PST. |
| TZH | Time zone hour displacement. |
| TZM | Time zone minute displacement. |
| TZR | Time zone region |
| X | Local radix character. In america this is a period (.) |
