Solved

GETTING TIME DIFFERENCE BETWEEN TWO DATES

Posted on 2007-12-06
7
7,245 Views
Last Modified: 2013-12-20
In my itemchanged event of a datawindow I'm calculating the time difference between two date fields for display purposes.  
Right now it's calculating the time difference and returns the time in hours. For instance, if datetime fields say start  = 1-1-07 10:00  end = 1-1-07 10:30 then result = 0.50
I need it to return 30 minutes for display.
I was able to get this working when user enter data like a time difference of 15,30,45,and 1 hour differfence ( i was able to convert 0.25, 0.75 , 0.50 properly ). however, i get inaccurate results when user enters something like
start  = 1-1-07 02:00  end = 1-1-07 02:57 then i want to display 57minutes, but i'm getting 54 minutes because of round of issues.

please help me with this logic, i'm close but it's not working in returning for all sorts of time differences.

thanks
0
Comment
Question by:badtz7229
7 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 20426655
How are you calculating the time difference in your code?

I am getting 57 mins with the following expression.

select round( ( to_date('1-1-07 02:57', 'dd-mm-yy hh24:MI') - to_date('1-1-07 02:00', 'dd-mm-yy hh24:MI')) * 24 * 60)
from dual
/
0
 

Author Comment

by:badtz7229
ID: 20427296
I am using the following powrbuilder code
if DaysAfter(startdate,enddate) = 0 and SecondsAfter(starttime,endtime) > 0 then
      ld_time_duration=SecondsAfter(starttime,endtime)/3600
      ld_total_duration = ld_time_duration
end if


long pos_of_decimal , hours
decimal  minutes
string s_total_time
pos_of_decimal = pos ( string ( ld_time_duration)  ,'.' )
hours=  LONG  (   mid (string (  ld_time_duration )  , 1, pos_of_decimal - 1)  )
minutes =   truncate ( dec ( mid ( string (  ld_time_duration )  , pos_of_decimal , 2)   ) * 60 , 0)
s_total_time = string ( hours)  + ':' + string(minutes)
0
 
LVL 14

Expert Comment

by:sandeep_patel
ID: 20444056
Hi,
I will suggest the option sujith80 said. That is for if you are using oracle datamase. In sybase and Microsoft SQL table name is 'dummy'. also you have to use date conversion functions accordingly.

But if you still want to go with PB code then here it is in Code Snippet. Please note that variables for days, hours, mins and seconds must be declared as Integer.

Regards,
Sandeep
date startdate,enddate

time starttime,endtime
 

integer li_hours,li_mins,li_seconds,li_days

long ll_time_duration
 

startdate = date('1-1-07')

enddate = date('1-1-07')
 

starttime = time('02:00:00')

endtime = time('03:57:15')
 

ll_time_duration=SecondsAfter(starttime,endtime)

li_days = DaysAfter(startdate,enddate)

li_hours = Integer(ll_time_duration / 3600)

li_mins = Integer(Mod(ll_time_duration,3660) / 60)

li_seconds = Mod(Mod(ll_time_duration,3600),60)

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 12

Expert Comment

by:Harish Varghese
ID: 20444087
Yes, you are almost there. but it is not really that hard to achieve this. Pls change the code like this.
Date startdate, enddate

Time starttime, endtime

Decimal ld_time_duration, ld_total_duration
 

startdate = Date (Left(em_1.Text, 10))

enddate = Date (Left (em_2.Text,10))

starttime = Time(Mid (em_1.Text,12))

endtime= Time(Mid(em_2.Text,12))
 

if DaysAfter(startdate,enddate) = 0 and SecondsAfter(starttime,endtime) > 0 then

      ld_time_duration=SecondsAfter(starttime,endtime)/*/3600*/

      ld_total_duration = ld_time_duration

end if
 
 

long pos_of_decimal , hours

decimal  minutes

string s_total_time
 

hours = Truncate (ld_time_duration / 3600, 0)

minutes = mod (ld_time_duration, 3600)  / 60

s_total_time = string ( hours)  + ':' + string(minutes)

messagebox ('s_total_time', s_total_time)
 

/*long pos_of_decimal , hours

decimal  minutes

string s_total_time

pos_of_decimal = pos ( string ( ld_time_duration)  ,'.' )

hours=  LONG  (   mid (string (  ld_time_duration )  , 1, pos_of_decimal - 1)  )

minutes =   truncate ( dec ( mid ( string (  ld_time_duration )  , pos_of_decimal + 1)   ) * 60 , 0) 

s_total_time = string ( hours)  + ':' + string(minutes)

*/

Open in new window

0
 

Accepted Solution

by:
badtz7229 earned 0 total points
ID: 20455066
thanks guys, i managed to do it another way, i ended up using the piece of code that i previously posted but changed some datatypes to use DECIMAL and also used ROUND to retrieve accuarate results; instead of LONG and TRUNCATE; to get time difference of each line item.

and then i used that same logic to sum the total of all line items using a computed field.
thanks anyway.
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20480470
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now