| Format String | Description |
| AM, A.M., |
|
| PM, P.M. | Meridian indicator. Use any of these format strings to specify AM and PM hours. AM and PM return the same values as A.M. and P.M. |
| D | Day of week (1-7), where Sunday equals 1. |
| DAY | Name of day, including up to nine characters (for example, Wednesday). |
| DD | Day of month (01-31). |
| DDD | Day of year (001-366, including leap years). |
| DY | Abbreviated three-character name for a day (for example, Wed). |
| HH, HH12 | Hour of day (01-12). |
| HH24 | Hour of day (00-23), where 00 is 12AM (midnight). |
| J | Modified Julian Day. Converts the calendar date to a string equivalent to its Modified Julian Day value, calculated from Jan 1, 4713 00:00:00 BC. It ignores the time component of the date. For example, the expression TO_CHAR( SHIP_DATE, 'J' ) converts Dec 31 1999 23:59:59 to the string 2451544. |
| MI | Minutes (00-59). |
| MM | Month (01-12). |
| MONTH | Name of month, including up to nine characters (for example, January). |
| MON | Abbreviated three-character name for a month (for example, Jan). |
| MS | Milliseconds (0-999). |
| NS | Nanoseconds (0-999999999). |
| Q | Quarter of year (1-4), where January to March equals 1. |
| RR | Last two digits of a year. The function removes the leading digits. For example, if you use 'RR' and pass the year 1997, TO_CHAR returns 97. When used with TO_CHAR, 'RR' produces the same results as, and is interchangeable with, 'YY.' However, when used with TO_DATE, 'RR' calculates the closest appropriate century and supplies the first two digits of the year. |
| SS | Seconds (00-59). |
| SSSSS | Seconds since midnight (00000 - 86399). When you use SSSSS in a TO_CHAR expression, the Integration Service only evaluates the time portion of a date. For example, the expression TO_CHAR(SHIP_DATE, 'MM/DD/YYYY SSSSS') converts 12/31/1999 01:02:03 to 12/31/1999 03783. |
| US | Microseconds (0-999999). |
| Y | Last digit of a year. The function removes the leading digits. For example, if you use 'Y' and pass the year 1997, TO_CHAR returns 7. |
| YY | Last two digits of a year. The function removes the leading digits. For example, if you use 'YY' and pass the year 1997, TO_CHAR returns 97. |
| YYY | Last three digits of a year. The function removes the leading digits. For example, if you use 'YYY' and pass the year 1997, TO_CHAR returns 997. |
| YYYY | Entire year portion of date. For example, if you use 'YYYY' and pass the year 1997, TO_CHAR returns 1997. |
| W | Week of month (1-5), where week 1 starts on the first day of the month and ends on the seventh, week 2 starts on the eighth day and ends on the fourteenth day. For example, Feb 1 designates the first week of February. |
| WW | Week of year (01-53), where week 01 starts on Jan 1 and ends on Jan 7, week 2 starts on Jan 8 and ends on Jan 14, and so on. |
| - / . ; : | Punctuation that displays in the output. You might use these symbols to separate date parts. For example, you create the following expression to separate date parts with a period: TO_CHAR( DATES, 'MM.DD.YYYY' ). |
| "text" | Text that displays in the output. For example, if you create an output port with the expression: TO_CHAR( DATES, 'MM/DD/YYYY "Sales Were Up"' ) and pass the date Apr 1 1997, the function returns the string '04/01/1997 Sales Were Up'. You can enter multibyte characters that are valid in the repository code page. |
| "" | Use double quotation marks to separate ambiguous format |
0Awesome Comments!