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.
2 posts Page 1 of 1

J Haskins

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

Post by 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?

Thanks!

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by 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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
2 posts Page 1 of 1