Convert Time To Number

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.

Convert Time To Number

Postby J Haskins » Wed Sep 11, 2013 11:20 am

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?

J Haskins
Posts: 5
Joined: Mon Apr 30, 2012 1:48 pm

Re: Convert Time To Number

Postby Support_Rick » Wed Sep 11, 2013 1:08 pm

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.
Rick Elliott
Sr. Product Specialist
(402) 944.4242
(800) 949-4696
Support Specialist
Posts: 335
Joined: Tue Jul 17, 2012 2:12 pm

Return to Community Forum

Who is online

Users browsing this forum: No registered users and 3 guests