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

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: Upcoming Events: IBIS 2018: Jun 18, BI & HANA 2018 Prague: Jun 29.

difference in time error


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
roninn75
Forum Member
Forum Member



Joined: 12 Nov 2016

Posts: 7



PostPosted: Thu May 17, 2018 12:39 am 
Post subject: difference in time error

good day

this is a follow up on the following thread:
http://www.forumtopics.com/busobj/viewtopic.php?t=242078

I used the following code to generate the difference in time:
Code:
=
(
(
(
ToNumber( Substr( FormatDate([SR_Created_At_UTC] ;"HH:mm:ss" ) ; 1 ; 2 ) ) * 60 * 60 +
ToNumber( Substr( FormatDate( [SR_Created_At_UTC] ;"HH:mm:ss" ) ; 4 ; 2 ) ) * 60 +
ToNumber( Substr( FormatDate( [SR_Created_At_UTC] ;"HH:mm:ss" ) ; 7 ; 2 ) )
)
-
(
ToNumber( Substr( FormatDate( [M_IR_Created_At] ; "HH:mm:ss" ) ; 1 ;2 ) ) * 60 * 60 +
ToNumber( Substr( FormatDate( [M_IR_Created_At] ; "HH:mm:ss" ) ; 4 ; 2) ) * 60 +
ToNumber( Substr( FormatDate( [M_IR_Created_At] ; "HH:mm:ss" ) ; 7 ; 2) )
)
)
) / 60 +
( DaysBetween( [M_IR_Created_At] ; [SR_Created_At_UTC] ) * 24 * 60 )


the results are out by a couple of seconds. an example is:
recent time - old time = x

result after calculation:
01 Apr 2018 1:55:51 PM - 01 Apr 2018 1:53:29 PM = 2:37

the results should be 02:22.

another example:
02 Apr 2018 8:12:45 PM - 02 Apr 2018 8:13:00 PM = 0:25

it should be 00:15

please assist.
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 10959
Location: Manchester, UK


flag
PostPosted: Thu May 17, 2018 2:12 am 
Post subject: Re: difference in time error

0.37 * 60 = 22.2

You're confusing minutes and decimal parts of an hour.

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
roninn75
Forum Member
Forum Member



Joined: 12 Nov 2016

Posts: 7



PostPosted: Thu May 17, 2018 3:08 am 
Post subject: Re: difference in time error

thank you for your response, I still don't know how to fix it though?
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 10959
Location: Manchester, UK


flag
PostPosted: Thu May 17, 2018 3:31 am 
Post subject: Re: difference in time error

You need to calculate it in two parts:

You have the whole number of minutes.

You then need to convert 37% of a minute into seconds - just multiply the remainder by 60

Have a look at this topic for further details:
http://www.forumtopics.com/busobj/viewtopic.php?t=117814

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
roninn75
Forum Member
Forum Member



Joined: 12 Nov 2016

Posts: 7



PostPosted: Thu May 17, 2018 7:51 am 
Post subject: Re: difference in time error

got it to work thank you for your assistance.
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 10959
Location: Manchester, UK


flag
PostPosted: Fri May 18, 2018 4:54 am 
Post subject: Re: difference in time error

roninn75 wrote:
got it to work thank you for your assistance.


No problem, thank you for letting us know. thumbsup.gif

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
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.0736 seconds using 17 queries. (SQL 0.0650 Parse 0.0002 Other 0.0083)
CCBot/2.0 (http://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