Conversion Functions

2.1      TO_CHAR:

The TO_CHAR function converts numeric values and dates to text strings. It is available in the Designer and the Workflow Manager.

                                    TO_CHAR( numeric_value )

TO_CHAR (date [, format ] )

Example : The following expression converts the values in the SALES port to text:

TO_CHAR (SALES )

SALES	RETURN VALUE
1800.03	'1800.03'
-22.57891	'-22.57891'

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'

If we omit the format_string argument, TO_CHAR returns a string in the default date format ‘MM/DD/YYYY’.

We can use Conversion functions with DATE functions in order to do some calculations.

The following composite expression converts the string DATE_PROMISED to date, adds 1 to it and then converts the same to text string with the format YYYYMMDD.

TO_CHAR(ADD_TO_DATE(TO_DATE(DATE_PROMISED),'DD',1),'YYYYMMDD')

Test functions can also be used with Conversion functions.

The following expression uses IS_DATE along with TO_CHAR.

IS_DATE(TO_CHAR(DATE_PROMISED,'YYYYMMDD'))

* TO_CHAR returns NULL if invalid Date is passed to the function.

2.2      TO_DATE:

The TO_DATE function converts a character string to a date datatype in the same format as the character string. It is available in the Designer and the Workflow Manager.

                        TO_DATE( string [, format ] )

Example : The following expression returns date values for the strings in the DATE_PROMISED port. TO_DATE always returns a date and time. If we pass a string that does not have a time value, the date returned always includes the time 00:00:00. If we execute a session in the twentieth century, the century will be 19. The current year on the machine running the Informatica Server is 1998:

TO_DATE( DATE_PROMISED, 'MM/DD/YY' )

DATE_PROMISED	RETURN VALUE
'12/28/81'	Dec 28 1981 00:00:00
NULL	NULL

The format of the string must exactly be the format given in the TO_DATE function.

* TO_DATE function fails if invalid date entries are given. To avoid this we must use IS_DATE function to check if the string has a valid date to be converted.

2.3      TO_DECIMAL:

The TO_DECIMAL function converts any value (except binary) to a decimal. It is available in the Designer.

                        TO_DECIMAL( value [, scale ] )

Example : This expression uses values from the port IN_TAX. The datatype is decimal with precision of 10 and scale of 3:

TO_DECIMAL( IN_TAX, 3 )

IN_TAX	RETURN VALUE
'15.6789'	15.678
NULL	NULL
'A12.3Grove'	0

We can also use two conversion functions together in a single expression.

The following expression uses the functions TO_DECIMAL and TO_CHAR.

TO_DECIMAL(TO_CHAR(DATE_PROMISED,'YYYYMMDD'))

2.4      TO_FLOAT:

The TO_FLOAT function converts any value (except binary) to a double-precision floating point number (the Double datatype). It is available in the Designer and the Workflow Manager.

                        TO_FLOAT( value )

Example : This expression uses values from the port IN_TAX:

TO_FLOAT( IN_TAX )

IN_TAX	RETURN VALUE
'15.6789'	15.6789
NULL	NULL

2.5      TO_INTEGER:

The TO_INTEGER function converts any value (except binary) to an integer by rounding the decimal portion of a value. It is available in the Designer and the Workflow Manager.

                        TO_INTEGER( value )

Example : This expression uses values from the port IN_TAX:

TO_INTEGER( IN_TAX )

IN_TAX	RETURN VALUE
'15.6789'	16
'60.2'	60