If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Convert a number to a time format

Started by newyork66, 29 Jul 2008 09:04:21 AM

Previous topic - Next topic

newyork66

Database column is a number ( It's actually a total milliseconds)
To be meaningful to the business, we need to display this as hh:mm:ss  (hh - hours, mm - minutes, ss - seconds). Ms / 1000 = Seconds. Seconds / 60 = Minutes (integer) + Seconds (remainder). Minutes / 60 = Hours (integer) + Minutes (remainder). No decimal places. So, 2,022,523 = 33:42 (no hours).
How can I achieve these in Cognos Report Studio?

m23

I would do it like this..

trunc(2022523/3600000)||':'||trunc(mod(2022523,3600000)/60000)||':'||trunc(mod(mod(2022523,3600000),60000)/1000)

trunc is an oracle function that just gets rid of the decimals. If your database isn't oracle using whatever your database has to round down to the nearest whole number would do the same thing. Someone may correct me but I don't think Cognos has any inbuilt function for rounding down.