OBIEE - Converting seconds to Minutes/Hours/Days
Days HH:MM:SS
CAST(FLOOR(“TimeInSeconds_Column” / 86400 ) AS VARCHAR(4)) || ‘Days ‘ || CAST(FLOOR(MOD(“TimeInSeconds_Column” , 86400 ) / 3600) AS VARCHAR(4)) ||’:'|| CAST(FLOOR(MOD(“TimeInSeconds_Column” , 3600 ) / 60) AS VARCHAR(4)) ||’:'|| CAST(MOD(“TimeInSeconds_Column” , 60) AS VARCHAR(4))
HH:MM:SS
CAST(FLOOR(MOD(“TimeInSeconds_Column” , 86400 ) / 3600) AS VARCHAR(4)) ||’:'|| CAST(FLOOR(MOD(“TimeInSeconds_Column” , 3600 ) / 60) AS VARCHAR(4)) ||’:'|| CAST(MOD(“TimeInSeconds_Column” , 60) AS VARCHAR(4))
Thanks,
Satya Ranki Reddy
HI,
ReplyDeleteI am getting error ..MOD function doesnt accept non text type.
Would appreciate for timely reponse.
Thanks
Rajveer
Hi Rajveer,
DeleteTry to apply cast function then apply the mode function.
Thanks,
Satay
This won't work when we are doing roll ups!!as we r casting this to varchar and that wont work; even though we cast it to Float, then there will be a problem with the rollups, as the hours can rollup to a number that is greater than 1440-- which should be actually a day.
ReplyDelete