Posted on 2007-12-06
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.

Question by:badtz7229
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 27

Expert Comment

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

Author Comment

ID: 20427296
I am using the following powrbuilder code
if DaysAfter(startdate,enddate) = 0 and SecondsAfter(starttime,endtime) > 0 then
      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)
LVL 14

Expert Comment

ID: 20444056
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.

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')
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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_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


Accepted Solution

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.

Expert Comment

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create Index on a Materialized View 5 59
Oracle encryption 12 70
oracle forms question 22 60
return value based on substr 10 71
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from Go to that link and select download selenium in the right hand columnThat will then direct you to their download page.From that page s…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

737 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