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!
Hi gatorfe
In what format is your time field? Is it just text? like '675:15'?
the field is stored as a varchar 00000:00
I think this will work for you:
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