Decimal to HH:MM:SS in Datastage

[sam334]

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.

[ShaneMuir]

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.

[sam334]

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

[ShaneMuir]

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

[atul9806]

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

[ShaneMuir]

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.