Type Conversion Functions in DataStage - Data Warehousing

Character Generation and Conversion

• Char(65) - Generates an ASCII character from its numeric code value. You can optionally specify the allow8bits argument to convert 8-bit ASCII values. The following example outputs the ASCII code 65 as the character 'A'.
• Seq("A") - Generates a numeric code value from an ASCII character. You can optionally specify the allow8bits argument to convert 8-bit ASCII values. The following example outputs the character 'A' as the ASCII code 65.

Date and Time Conversion

• DateToString(mylink.mydate, “%dd:%mm:%yyyy”) - Outputs the date contained in the column mylink.mydate to a string with the format dd:mm:yyyy. If mylink.mydate contained the date 18th August, 2009, then the output string would be “18:08:2009”.
• StringToDate(mylink.mystring,"%dd:%mm:%yyyy") - If the column mylink.mystring contains the string "18:08:1958", then this function returns the date 1958–08–18.
• DateToDecimal (mylink.basedate, “%dd%mm%yyyy”) - If the column mylink.basedate contains the date 2012-08-18, then this function stores the date as the decimal number 18082012.
• DecimalToDate (mylink.basedate, “%dd%mm%yyyy”) - Converts a decimal value to a date in the format ddmmyyyy. For example, it converts 18082012 to 18-08-2012.
• StringToTime(mylink.mystring,"%(h,s):$(n,s):$(s,s)") - Converts a string representation of time into a time value. The time format should match the provided pattern, where (h,s) represents hours and seconds, and $(n,s) represents minutes and seconds.

Numeric Validation Functions

• IsValidDecimal(mylink.mynum) - Returns 1 if the column mylink.mynum contains a valid decimal number, and 0 otherwise.
• IsValidDate(mylink.mydate) - Returns 1 if the column mylink.mydate contains a valid date, and 0 otherwise.
• IsValidTime(mylink.mytime) - Returns 1 if the column mylink.mytime contains a valid time value, and 0 otherwise.
• IsValidTimestamp(mylink.mytimestamp) - Returns 1 if the column mylink.mytimestamp contains a valid timestamp, and 0 otherwise.

DataStage Type Conversion Functions

In DataStage, type conversion functions enable you to convert one data type into another during the transformation of data. This article provides an overview of various built-in DataStage type conversion functions and examples of their usage.

Built-In Type Conversion Functions

• TO_CHAR(): Converts a numeric, date or timestamp data type to a character string.
• TO_DATE(): Converts a character string representing a date in various formats to the DATE data type.
• TO_TIMESTAMP(): Converts a character string representing a timestamp in various formats to the TIMESTAMP data type.
• TO_NUMBER(): Converts a character string or date data type to a numeric data type.

Example: Converting a Character String to a Date using TO_DATE()

        <Transformation>
            <Input>
                <ColumnList>
                    <Column name="date" type="VARCHAR"/>
                </ColumnList>
            </Input>
            <Output>
                <ColumnList>
                    <Column name="converted_date" type="DATE"/>
                </ColumnList>
            </Output>
            <TransformationTask>
                <SQL>
                    SELECT date AS "date", TO_DATE(date, 'YYYY-MM-DD') AS "converted_date" FROM input_table;
                </SQL>
            </TransformationTask>
        </Transformation>
    

Example: Converting a Date to a Character String using TO_CHAR()

        <Transformation>
            <Input>
                <ColumnList>
                    <Column name="date" type="DATE"/>
                </ColumnList>
            </Input>
            <Output>
                <ColumnList>
                    <Column name="converted_date_str" type="VARCHAR"/>
                </ColumnList>
            </Output>
            <TransformationTask>
                <SQL>
                    SELECT date AS "date", TO_CHAR(date, 'YYYY-MM-DD') AS "converted_date_str" FROM input_table;
                </SQL>
            </TransformationTask>
        </Transformation>
    

Conclusion

DataStage provides several built-in type conversion functions to convert data types during the transformation process. Understanding and mastering these functions will aid in creating efficient ETL processes.