Saturday, August 25, 2012

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


3 comments:

  1. HI,
    I am getting error ..MOD function doesnt accept non text type.

    Would appreciate for timely reponse.

    Thanks
    Rajveer

    ReplyDelete
    Replies
    1. Hi Rajveer,

      Try to apply cast function then apply the mode function.

      Thanks,
      Satay

      Delete
  2. 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