Function Reference > Dates > TO_CHAR format strings
  

TO_CHAR format strings

The TO_CHAR function converts a Date/Time datatype to a string with the format you specify. You can convert the entire date or a part of the date to a string. You might use TO_CHAR to convert dates to string, changing the format for reporting purposes.
TO_CHAR is generally used when the target is a flat file or a database that does not support a Date/Time datatype.
The following table summarizes the format strings for dates in the function TO_CHAR:
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.
DD
Day of month (01-31).
DDD
Day of year (001-366, including leap years).
DAY
Name of day, including up to nine characters (for example, Wednesday).
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).
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, Data Integration 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.
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 might 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 have 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 strings, for example D”D””DDD. The empty quotation marks do not appear in the output.
Note: The format string is not case sensitive. It must always be enclosed within single quotation marks.

Examples

The following examples illustrate the J, SSSSS, RR, and YY format strings. See the individual functions for more examples.
Note: The application ignores the time portion of the date in a TO_CHAR expression.

J format string

Use the J format string in a TO_CHAR expression to convert date values to MJD values expressed as strings. For example:
TO_CHAR(SHIP_DATE, 'J')
SHIP_DATE
RETURN_VALUE
Dec 31 1999 23:59:59
2451544
Jan 1 1900 01:02:03
2415021

SSSSS format string

You can also use the format string SSSSS in a TO_CHAR expression. For example, the following expression converts the dates in the SHIP_DATE port to strings representing the total seconds since midnight:
TO_CHAR( SHIP_DATE, 'SSSSS')
SHIP_DATE
RETURN_VALUE
12/31/1999 01:02:03
3783
09/15/1996 23:59:59
86399

RR format string

The following expression converts dates to strings in the format MM/DD/YY:
TO_CHAR( SHIP_DATE, 'MM/DD/RR')
SHIP_DATE
RETURN_VALUE
12/31/1999 01:02:03
12/31/99
09/15/1996 23:59:59
09/15/96
05/17/2003 12:13:14
05/17/03

YY format string

In TO_CHAR expressions, the YY format string produces the same results as the RR format string. The following expression converts dates to strings in the format MM/DD/YY:
TO_CHAR( SHIP_DATE, 'MM/DD/YY')
SHIP_DATE
RETURN_VALUE
12/31/1999 01:02:03
12/31/99
09/15/1996 23:59:59
09/15/96
05/17/2003 12:13:14
05/17/03