BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

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

Goto page 1, 2, 3  Next
 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Darren Griffin
Senior Member
Senior Member



Joined: 10 Jul 2002

Posts: 88
Location: Room 101



PostPosted: Thu May 29, 2003 5:35 am 
Post subject: Converting a number into an HH:MM:SS time format - Oracle

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.

Code:
to_char(trunc(sum(<seconds_column>)/3600), 'FM999999990')  || ':' || to_char(trunc(mod(sum((<seconds_column>),3600)/60), 'FM00') || ':' || to_char(mod(sum((<seconds_column>),60), 'FM00')


This now renders a numeric value as a time in hours, minutes and seconds. It took me a while, and there doesn't seem to be anything along these lines on the forum anywhere, so I thought I'd share it. Hopefully, it may be of use to someone else.

Darren.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22124
Location: Dallas, Texas


flag
PostPosted: Thu May 29, 2003 12:51 pm 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

Darren Griffin wrote:
This now renders a numeric value as a time in hours, minutes and seconds. It took me a while, and there doesn't seem to be anything along these lines on the forum anywhere, so I thought I'd share it. Hopefully, it may be of use to someone else.

And I'm sure it will, assuming that "someone else" remembers to search. icon_lol.gif Seriously, thanks for posting. It's nice when someone posts a solution instead of a problem every now and then.

Dave

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
slimdave
Principal Member
Principal Member



Joined: 10 Sep 2002

Posts: 219
Location: Bracknell, UK


flag
PostPosted: Thu May 29, 2003 4:37 pm 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

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

Code:
to_char(trunc(sum(<seconds_column>)/3600), 'FM999999990')  || ':' || to_char(trunc(mod(sum(<seconds_column>),3600)/60), 'FM00') || ':' || to_char(mod(sum(<seconds_column>),60), 'FM00')
Back to top
LotusSutol
Senior Member
Senior Member



Joined: 30 Jun 2003

Posts: 33



PostPosted: Mon Mar 29, 2004 3:31 pm 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

This just helped me out of a jam...thanks!
_________________
Michael P.
Aera Energy LLC
Bakersfield, CA.
Back to top
ken.gaul
Data Migration Team
Data Migration Team



Joined: 18 Jun 2002

Posts: 268
Location: Edinburgh, Scotland, UK


flag
PostPosted: Tue Mar 30, 2004 3:39 am 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

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.

_________________
K.

Life is tough...Then you get some Users!
Back to top
BOB Downloads
Download Manager
Download Manager



Joined: 05 May 2003

Posts: 40



PostPosted: Tue Mar 30, 2004 9:50 am 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

ken.gaul wrote:
I've sent it in for inclusion onto the BOB downloads

I just wanted to let you know that I've checked the bobdownloads@forumtopics.com email address, and haven't received the file.
Back to top
ken.gaul
Data Migration Team
Data Migration Team



Joined: 18 Jun 2002

Posts: 268
Location: Edinburgh, Scotland, UK


flag
PostPosted: Tue Mar 30, 2004 9:52 am 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

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

Life is tough...Then you get some Users!
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22124
Location: Dallas, Texas


flag
PostPosted: Tue Mar 30, 2004 11:00 am 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

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

Ken: We changed the email address when we changed the name of the forum. Please use the newer address, thanks. icon_cool.gif

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
ken.gaul
Data Migration Team
Data Migration Team



Joined: 18 Jun 2002

Posts: 268
Location: Edinburgh, Scotland, UK


flag
PostPosted: Tue Mar 30, 2004 11:20 am 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

Consider it done! Well actually don't consider it done it is done icon_wink.gif
_________________
K.

Life is tough...Then you get some Users!
Back to top
BOB Downloads
Download Manager
Download Manager



Joined: 05 May 2003

Posts: 40



PostPosted: Fri Apr 02, 2004 8:43 am 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

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

icon_mrgreen.gif Thanks Ken icon_mrgreen.gif
Back to top
patwehr
Forum Member
Forum Member



Joined: 05 Oct 2004

Posts: 2
Location: Buffalo, NY



PostPosted: Wed Oct 13, 2004 12:16 pm 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

Ken,

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

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 icon_confused.gif
Back to top
ken.gaul
Data Migration Team
Data Migration Team



Joined: 18 Jun 2002

Posts: 268
Location: Edinburgh, Scotland, UK


flag
PostPosted: Thu Oct 14, 2004 3:51 am 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

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 icon_sad.gif
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.

_________________
K.

Life is tough...Then you get some Users!
Back to top
viswa
Senior Member
Senior Member



Joined: 26 Aug 2002

Posts: 57
Location: chicago,il



PostPosted: Fri Oct 22, 2004 4:13 pm 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

Hi,

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

Thanks,
Viswa
Back to top
dolap
Forum Member
Forum Member



Joined: 22 Feb 2003

Posts: 4
Location: Bologna - Italy


flag
PostPosted: Mon Nov 29, 2004 11:36 am 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

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
Back to top
ken.gaul
Data Migration Team
Data Migration Team



Joined: 18 Jun 2002

Posts: 268
Location: Edinburgh, Scotland, UK


flag
PostPosted: Mon Nov 29, 2004 12:47 pm 
Post subject: Re: Converting a number into an HH:MM:SS time format - Oracl

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.
_________________
K.

Life is tough...Then you get some Users!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 3 All times are GMT - 5 Hours
Goto page 1, 2, 3  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0909 seconds using 17 queries. (SQL 0.0030 Parse 0.0602 Other 0.0277)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo