| Aspect | Parameter Description | Example |
| Century | CC | Two-digit century. |
| 21 | Century | SCC |
| Two-digit century with a negative sign (C) for B.C. | -10 | Quarter |
| Q | One-digit quarter of the year. | 1 |
| Year | YYYY | All four digits of the year. |
| 2006 | Year | IYYY |
| All four digits of the ISO year. | 2006 | Year |
| RRRR | All four digits of the rounded year, which depends on the current year. | 2006 |
| Year | SYYYY | All four digits of the year with a negative sign (C) for B.C. |
| C1001 | Year | Y,YYY |
| All four digits of the year with a comma. | 2,006 | Year |
| YYY | Last three digits of the year. | 006 |
| Year | IYY | Last three digits of the ISO year. |
| 006 | Year | YY |
| Last two digits of the year. | 06 | Year |
| IY | Last two digits of the ISO year. 06 | Year |
| RR | Last two digits of the rounded year, which depends on the current year. | 06 |
| Year | Y | Last digit of the year. |
| 6 | Year | I |
| Last digit of the ISO year. | 6 | Year |
| YEAR | Name of the year in uppercase. | TWO THOUSAND-SIX |
| Year | Year | Name of the year with the first letter in uppercase. |
| Two Thousand-Six | Month | MM |
| Two-digit month of the year. | 01 | Month |
| MONTH | Full name of the month in uppercase, right-padded with spaces to a total length of nine characters. | JANUARY |
| Month | Month | Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters. |
| January | Month | MON |
| First three letters of the name of the month in uppercase. | JAN | Month |
| Mon | First three letters of the name of the month with the first letter in uppercase. | Jan |
| Month | RM | Roman numeral month. |
| The Roman numeral month for the fourth month (April) is IV. | Week | WW |
| Two-digit week of the year. | 02 | Week |
| IW | Two-digit ISO week of the year. | 02 |
| Week | W | One-digit week of the month. |
| 2 | Day | DDD |
| Three-digit day of the year. | 103 | Day |
| DD | Two-digit day of the month. | 31 |
| Day | D | One-digit day of the week. |
| 5 | Day | DAY |
| Full name of the day in uppercase. | SATURDAY | Day |
| Day | Full name of the day with the first letter in uppercase. | Saturday |
| Day | DY | First three letters of the name of the day in uppercase. |
| SAT | Day | Dy |
| First three letters of the name of the day with the first letter in uppercase. | Sat | Day |
| J | Julian day-the number of days that have passed since January 1, 4713 B.C. | 2439892 |
| Hour | HH24 | Two-digit hour in 24-hour format. |
| 23 | Hour | HH |
| Two-digit hour in 12-hour format. | 11 | Minute |
| MI | Two-digit minute. | 57 |
| Second | SS | Two-digit second. |
| 45 | Second | FF[1..9] |
| Fractional seconds with an optional number of digits to the right of the decimal point. Only applies timestamps,When dealing with 0.123456789 seconds, FF3 would round to 0.123. | Second | SSSSS |
| Number of seconds past 12 a.m. | 46748 | Second |
| MS | Millisecond (millionths of a second). | 100 |
| Second | CS | Centisecond (hundredths of a second). |
| 10 | Separators | -/,.;: "text" |
| Characters that allow you to separate the aspects of a date and time. You can supply freeform text in quotes as a separator. | When dealing with the date December 13, 1969, DD-MM-YYYY would produce 12-13-1969 and DD/MM/YYYY would produce 12/13/1969 | Suffixes |
| AM or PM | AM or PM as appropriate. | AM |
| Suffixes | A.M. or P.M. | A.M. or P.M. as appropriate. |
| P.M. | Suffixes | AD or BC |
| AD or BC as appropriate. | AD | Suffixes |
| A.D. or B.C. | A.D. or B.C. as appropriate. | B.C. |
| Suffixes | TH | Suffix to a number. You can make the suffix uppercase by specifying the numeric format in uppercase and vice versa for lowercase.When dealing with a day number of 28, ddTH would produce 28th and DDTH would produce 28TH |
| Suffixes | SP | Number is spelled out.When dealing with a day number of 28, DDSP would produce TWENTY-EIGHT and ddSP would produce twenty-eight |
| Suffixes | SPTH | Combination of TH and SP.When dealing with a day number of 28, DDSPTH would produce TWENTY-EIGHTH and ddSPTH would produce twenty-eighth |
| Era | EE | Full era name for Japanese Imperial, ROC Official, and Thai Buddha calendars. |
| No example | Era | E |
| Abbreviated era name. | No example | Time zones |
| TZH | Time zone hour. | 12 |
| Time zones | TZM | Time zone minute. |
| 30 | Time zones | TZR |
| Time zone region. | PST | Time zones |
| TZD | Time zone with daylight savings information. | No example |
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
| 13.21.TO_DATE | ||||
| 13.21.1. | Specifying a Datetime Format for TO_DATE() function | |||
| 13.21.2. | Using the YY Format | |||
| 13.21.3. | Using the RR Format | |||
| 13.21.4. | TO_CHAR(TO_DATE('04-JUL-15', 'DD-MON-RR'), 'DD-MON-YYYY') | |||
| 13.21.5. | TO_DATE function to convert from characters to dates explicitly | |||
| 13.21.6. | TO_DATE(x[, format]) converts the x string to a datetime | |||
| 13.21.7. | TO_DATE() converts the strings 04-JUL-2006 to the date July 4, 2006 | |||
| 13.21.8. | Specifying Times | |||
| 13.21.9. | TO_DATE() with INSERT statement | |||
| 13.21.10. | DBMS_OUTPUT.PUT_LINE(TO_DATE ('1/1')); | |||
| 13.21.11. | DBMS_OUTPUT.PUT_LINE(TO_DATE ('6/1996')); | |||
| 13.21.12. | DBMS_OUTPUT.PUT_LINE(TO_DATE ('12-APR-09')); | |||
| 13.21.13. | DBMS_OUTPUT.PUT_LINE(TO_DATE ('19991205')); | |||
| 13.21.14. | DBMS_OUTPUT.PUT_LINE(TO_DATE ('1/1/1')) | |||
| 13.21.15. | Birthday before 1940 | |||
| 13.21.16. | Catch exception from to_date function | |||