asked on

GETTING TIME DIFFERENCE BETWEEN TWO DATES

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

Sujith

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
/

tesmc

ASKER

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)

sandeep_patel

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

Harish Varghese

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

ASKER CERTIFIED SOLUTION

tesmc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vee_Mod

Closed, 500 points refunded.
Vee_Mod
Community Support Moderator