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.
Decimal to HH:MM:SS in Datastage
Moderators: chulett, rschirm, roy
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
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
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
~Atul Singh
<a href=http://www.datagenx.net>DataGenX</a> | <a href=https://www.linkedin.com/in/atulsinghds>LinkedIn</a>
<a href=http://www.datagenx.net>DataGenX</a> | <a href=https://www.linkedin.com/in/atulsinghds>LinkedIn</a>
Actually that was there in my solution.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
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.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)