Converting a number into an HH:MM:SS time format - Oracle

Darren Griffin

I have a table in my database that has a NUMBER(12) column value representing an amount of time in seconds. For a report I'm building, I'd like to format this in an hours : minutes : seconds style.

Initially, I thought I could achieve this using the Oracle TO_DATE and TO_CHAR functions, but I couldn't, as some of the seconds values represent an hours value of over 24 hours, and any amount of time greater than 24 hours cannot be rendered in an Oracle hh:mm:ss format.

(Times over 24 hours can only be displayed as a number of days).

So, I built this bit of SQL and created a measure object with a CHARACTER type.

Dave Rathbun

slimdave

You've just got a couple of spare parentheses in there ...

LotusSutol · Senior Member Joined: 30 Jun 2003 Posts: 33

This just helped me out of a jam...thanks!

ken.gaul

I've got an extension DLL for this as all that to_char/trunc stuff drives me mad if you have to do it alot.
I've sent it in for inclusion onto the BOB downloads but It won't be there for a while. Let me know if you want it. (Only tested on 5.x)
see this for basic details and e-mail me if you would like it.. or wait till it appears in the downloads.

BOB Downloads · Download Manager Joined: 05 May 2003 Posts: 40

ken.gaul

The instructions in the original post said codesamples@forumtopics.com will that work or do you want me to re-submit?

Dave Rathbun

ken.gaul

Consider it done! Well actually don't consider it done it is done

BOB Downloads · Download Manager Joined: 05 May 2003 Posts: 40

The dll is available here. Further discussion should take place in this topic.

Thanks Ken

patwehr

Ken,

Thanks for the download - the DLLs work great on my PC.

I have posted the boextensions.dll and the extfunct.txt to the UserLibs directory on our 3 BO servers.

I published a report that uses FormatAsTime to Corporate Documents. When a user accesses the report on WebI, the time is formatted correctly. When using ZABO, it doesn't work. Is there a way for ZABO installed machines to take advantage of this?

Note: Even though extfunct.txt declaring these 2 functions has been copied to the UserLibs folder on each of the 3 BO servers, a different extfunct.txt file gets copied to the users ZABO PC UserLibs directory which doesn't have the custom functions. (Seems to copy when they refresh the report).

Do I need to have ALL ZABO users put these 2 files on their C: drive under UserLibs or is there another way? Is there somewhere else I need to store the extfunct.txt file?

Thanks much,
Pat

ken.gaul

I'm not sure I can help there. I don't use Zabo here and I'm not sure I remember how and what it downloads. Copying the DLL to every zabo machine is an option obviously not ideal though

There might be a registry thing you can change which could point to a network drive for UserLibs locations, but that is still a per desktop fix.

Might need to post a more generic Zabo rollout question to see if anybody else knows a better way.

Sorry I can't be more help.

viswa

Hi,

I am working with BO 61B AND SYBASE 12.Can you please guide me how to achieve this in sybase.

Thanks,
Viswa

dolap

Ken,

thank you very much for the boextensions.dll. It works very well with BO 5.x.

I'm testing it with the new version BO 6.5, but it dosn't seem to work.

Do you have a new dll working with BO 6.5 ??

Thank you very much,

Dolap

ken.gaul

Not yet I haven't got 6.5, I was working on a 6.1 one but am having trouble with it. I'll post as soon as I have something to work with. Hopefully in the next couple of weeks.