DSXchange

All,
We are pulling a column from DB2 database as 2.2 but in Sequential file it suppose to be deliverable as 00:02:02. So, its actually 2 minutes and 2 seconds format which needs to convert to HH:MM:SS.

Any clue how to achieve this.

Thanks.

So it is stored in your DB as a decimal? Does this mean that that the highest number available is 59.59?

If that is the case, just use a string manipulation to separate the parts and output as the required string.

Yes. It would be even that much. May be not more than 10 minutes and its average time. Thanks.

So it becomes a simple string manipulation.
In a transformer
1. Convert your input decimal to a string value, using DecimalToString() remember to add the option to suppress_zero
2. Use Field() to split the converted value into separate parts (ie mins and seconds). Remember to pad your values to 2 characters long (easy way is to use Right('00':Value,2)
3. Construct your output as '00' : Minute Value : Second Value

Just one addon to ShaneMuir solution -
You need to add 0 in your minutes and seconds if they are single digit, So add zero by checking if len(val)=1 else val

atul9806 wrote:Just one addon to ShaneMuir solution -
You need to add 0 in your minutes and seconds if they are single digit, So add zero by checking if len(val)=1 else val

Actually that was there in my solution.

2. Use Field() to split the converted value into separate parts (ie mins and seconds). Remember to pad your values to 2 characters long (easy way is to use Right('00':Value,2)

Rather than an If Then Else statement I suggested to use just Right('00':Value,2) which will always select 2 characters no matter what value is provided.