TO_CHAR (Dates)
Converts dates to character strings. TO_CHAR also converts numeric values to strings. You can convert the date into any format using the TO_CHAR format strings.
TO_CHAR (date [,format]) converts a data type or internal value of date, Timestamp, Timestamp with Time Zone, or Timestamp with Local Time Zone data type to a value of string data type specified by the format string.
Syntax
TO_CHAR( date [,format] )
The following table describes the arguments for this command:
Argument | Required/ Optional | Description |
|---|
date | Required | Date/Time datatype. Passes the date values you want to convert to character strings. You can enter any valid transformation expression. |
format | Optional | Enter a valid TO_CHAR format string. The format string defines the format of the return value, not the format for the values in the date argument. If you omit the format string, the function returns a string based on the date format specified in the session. |
Return Value
String.
NULL if a value passed to the function is NULL.
Examples
The following expression converts the dates in the DATE_PROMISED port to text in the format MON DD YYYY:
TO_CHAR( DATE_PROMISED, 'MON DD YYYY' )
DATE_PROMISED | RETURN VALUE |
|---|
Apr 1 1998 12:00:10AM | 'Apr 01 1998' |
Feb 22 1998 01:31:10PM | 'Feb 22 1998' |
Oct 24 1998 02:12:30PM | 'Oct 24 1998' |
NULL | NULL |
If you omit the format argument, TO_CHAR returns a string in the date format specified in the session, by default, MM/DD/YYYY HH24:MI:SS.US:
TO_CHAR( DATE_PROMISED )
DATE_PROMISED | RETURN VALUE |
|---|
Apr 1 1998 12:00:10AM | '04/01/1998 00:00:10.000000' |
Feb 22 1998 01:31:10PM | '02/22/1998 13:31:10.000000' |
Oct 24 1998 02:12:30PM | '10/24/1998 14:12:30.000000' |
NULL | NULL |
The following expressions return the day of the week for each date in a port:
TO_CHAR( DATE_PROMISED, 'D' )
DATE_PROMISED | RETURN VALUE |
|---|
04-01-1997 12:00:10AM | '3' |
02-22-1997 01:31:10PM | '7' |
10-24-1997 02:12:30PM | '6' |
NULL | NULL |
TO_CHAR( DATE_PROMISED, 'DAY' )
DATE_PROMISED | RETURN VALUE |
|---|
04-01-1997 12:00:10AM | 'Tuesday' |
02-22-1997 01:31:10PM | 'Saturday' |
10-24-1997 02:12:30PM | 'Friday' |
NULL | NULL |
The following expression returns the day of the month for each date in a port:
TO_CHAR( DATE_PROMISED, 'DD' )
DATE_PROMISED | RETURN VALUE |
|---|
04-01-1997 12:00:10AM | '01' |
02-22-1997 01:31:10PM | '22' |
10-24-1997 02:12:30PM | '24' |
NULL | NULL |
The following expression returns the day of the year for each date in a port:
TO_CHAR( DATE_PROMISED, 'DDD' )
DATE_PROMISED | RETURN VALUE |
|---|
04-01-1997 12:00:10AM | '091' |
02-22-1997 01:31:10PM | '053' |
10-24-1997 02:12:30PM | '297' |
NULL | NULL |
The following expressions return the hour of the day for each date in a port:
TO_CHAR( DATE_PROMISED, 'HH' )
TO_CHAR( DATE_PROMISED, 'HH12' )
DATE_PROMISED | RETURN VALUE |
|---|
04-01-1997 12:00:10AM | '12' |
02-22-1997 01:31:10PM | '01' |
10-24-1997 02:12:30PM | '02' |
NULL | NULL |
TO_CHAR( DATE_PROMISED, 'HH24' )
DATE_PROMISED | RETURN VALUE |
|---|
04-01-1997 12:00:10AM | '00' |
02-22-1997 01:31:10PM | '13' |
10-24-1997 11:12:30PM | '23' |
NULL | NULL |
The following expression converts date values to MJD values expressed as strings:
TO_CHAR( SHIP_DATE, 'J')
SHIP_DATE | RETURN_VALUE |
|---|
Dec 31 1999 03:59:59PM | 2451544 |
Jan 1 1900 01:02:03AM | 2415021 |
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:03AM | 12/31/99 |
09/15/1996 03:59:59PM | 09/15/96 |
05/17/2003 12:13:14AM | 05/17/03 |
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:03AM | 3783 |
09/15/1996 03:59:59PM | 86399 |
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:03AM | 12/31/99 |
09/15/1996 03:59:59PM | 09/15/96 |
05/17/2003 12:13:14AM | 05/17/03 |
The following expression returns the week of the month for each date in a port:
TO_CHAR( DATE_PROMISED, 'W' )
DATE_PROMISED | RETURN VALUE |
|---|
04-01-1997 12:00:10AM | '01' |
02-22-1997 01:31:10AM | '04' |
10-24-1997 02:12:30PM | '04' |
NULL | NULL |
The following expression returns the week of the year for each date in a port:
TO_CHAR( DATE_PROMISED, 'WW' )
DATE_PROMISED | RETURN VALUE |
|---|
04-01-1997 12:00:10PM | '18' |
02-22-1997 01:31:10AM | '08' |
10-24-1997 02:12:30AM | '43' |
NULL | NULL |
Tip
You can combine TO_CHAR and TO_DATE to convert a numeric value for a month into the text value for a month using a function such as:
TO_CHAR( TO_DATE( numeric_month, 'MM' ), 'MONTH' )