DSXchange

All, Have a question on time conversion.

From DB2 DB we pulled calculated some total call time and columns are like

Tot_Time Tot.Attend
1.22 1.51

I need to have hh:mm:ss format. Is there any way we can convert it in datastage.

It should be,

00:01:22 and 00:01:51

Thanks,

Your TOT fields are always MM.SS or can they contain an hour value as well?

It is always be 1 minute 22 seconds. The way data is right now, it is not going to be hour.

I mean, it will be minute and second.

Are you looking for something... fancy? You could always use Field() to split the minutes and seconds then concat the results together. Left pad zeroes onto the short numbers.

You can use StringToTime as well. With just %nn:%ss to map it to a time field.
But you ll need a padding of zero anyway.

This looks like a Server job question; StringToTime is a parallel function.

So just to confirm, what does the actual input data look like for call times over an hour? That example makes a difference in your logic.

He noted there would never be an hour component, so always minutes and seconds less than one hour is how I read that.

Yes it just leaves a little too much up to my imagination.

A really, really bad call in real life could go on for 50 hours and 30 seconds. Just curious in the data if that would look like 3000.30 or if calls > 59 minutes may be filtered out. If the latter, then the conversion logic is simpler.

All,
It is a calculation of time taken to attend a call. So, there are actually all calculations in SQL query which gives the result of this. I found from january the value never crosses minutes border.

So, we believe, it will be always m.ss format.

I'd use a Fmt() function. Something like

sam334 wrote:So, we believe, it will be always m.ss format.

It's good to believe... better to be certain.

It will be a concatenation of Certain and Believe

I used the FMT code above. The output is 00:00. The input column 1.15 and 1.22 are varchar(1) and varchar(3). And the output data type is same. Do i need to change the output data type to time or anything..

Thanks.

Varchar 1 & 3? How does that work? Seems to me your fields would need to be Varchar 5 to accomodate a full value - double digits for both plus the delimiter. And the target would need to be 8.

An alternative to Ray's fancy FMT:
All off the top of my head so YMMV.

sam334 wrote:Do i need to change the output data type to time or anything

You tell us... what is your actual target? I've been assuming a string.