Converting hh:mm field to dd:hh

[gatorfe]

Hello, there is a field from the ERP system that is brought into a cognos report that shows hh:mm between shop floor transactions. The Cognos users want to see that field in dd:hh format instead on the report. Is there a way of doing this? Thanks in advance for any suggestions!

[jcrouch]

Hi gatorfe

In what format is your time field? Is it just text? like '675:15'?

[gatorfe]

the field is stored as a varchar 00000:00

[jcrouch]

I think this will work for you:

Code Select Expand

cast(cast(((cast(substring([yourField], 1, position(':', [yourField])-1), INTEGER)*60)+cast(substring([yourField], position(':', [yourField])+1, 2), INTEGER))/1440, INTEGER), VARCHAR(10)) +
':' + cast(cast(mod(((cast(substring([yourField], 1, position(':', [yourField])-1), INTEGER)*60)+cast(substring([yourField], position(':', [yourField])+1, 2), INTEGER)),1440)/60, INTEGER), VARCHAR(10)) +
':' + cast(mod(((cast(substring([yourField], 1, position(':', [yourField])-1), INTEGER)*60)+cast(substring([yourField], position(':', [yourField])+1, 2), INTEGER)), 60), CHAR(2))

It is multiplying your hours out to minutes and adding the remaining minutes to that. Then by dividing that back out and getting the mod we can get the days:hours:minutes back out of it. If you do not want the minutes you can just leave off the last part.

Let me know if this works for you,

Jeremy