Convert Time To Number

[J Haskins]

I'm reading in a time value from a CSV file that is formated as HH:mm:ss. I want to strip out the colons and store the results in a numeric (packed decimal) database field. For example if the value in the CSV file is "19:01:20", the value in the database field would be 190120.

On my Read CSV task, I have the time column defined as:

Code: Select all

<column index="7" name="TTIME" type="TIME" pattern="HH:mm:ss" trim="both" />

In my SQL task, I have the parameter that corresponds to the field where this value should be inserted defiled as:

Code: Select all

<param index="7" type="NUMERIC" mapFrom="7" nullSubstitute="0" />

When I run the project I get a data type mismatch error. Is there another way to do this?

Thanks!

[Support_Rick]

Ms Haskins,

Please try doing your insert with a REPLACE function, something like:

${ Replace( MyField, ':', '' ) }

This should give you exactly the numeric value you need. Although, it will not give you a leading zero for time under 10.