Convert HH:mm:ss to only seconds

building reports webintelligence
1

I have two coloumns (stage 1 & stage 2) with time in the HH:mm:ss -format. I would like to convert these two coloumns into seconds in order to easier get the difference. I have tried with FormatNumber but either is my syntax wrong or I could use another function. Suggestions?

Bikern (BOB member since 2011-01-10)

2

Can you try doing this at the universe level, in sql?

Marfi :poland: (BOB member since 2006-12-18)

3

Yes, I could try to fix that in Universe as well, but I would like to keep that particular data as it is and have the option to play around with it in WebI for report purpose. That’s why I want to convert it into seconds… :slight_smile:

Bikern (BOB member since 2011-01-10)

4

Hi,

Try something like this:

ToNumber(FormatDate(<ORDER_DATE> ,"HH")) * 3600 +
ToNumber(Left(FormatDate(<ORDER_DATE> ,"mm:ss") ,2)) * 60 +
ToNumber(FormatDate(<ORDER_DATE> ,"ss"))

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

Thanks, and for other newbies like me, - with the adjustment of the “,” to “;” instead it worked very nice. This saved me some time!

Bikern (BOB member since 2011-01-10)

6

Yes. The above formula is for DeskI, not for WebI.

Marek Chladny :slovakia: (BOB member since 2003-11-27)