Former Member

### FINDING DATETIME DIFFERENCE

Hi

I have an issue. I have two datetime fields dt1 and dt2. I require the difference between these two.

I used the SecondsAfter Function. It is working ok when I am obtaining the difference between the time on any one day.

But suppose dt1 is '30/12/2014 23:30' and dt2 is '31/12/2014 00:30' then how to obtain the difference?.

Regards

JAIMOHAN

10|10000 characters needed characters exceeded

Former Member
Dec 30, 2014 at 05:12 AM

Try this function:

decimal ll_hours

//ll_hours = DaysAfter (date (ldt_1), date (ldt_2)) * 24.0 + SecondsAfter(time (ldt_1), time (ldt_2)) / 3600.0

ll_hours = DaysAfter (date (ldt_2), date (ldt_1)) * 24.0 + SecondsAfter(time (ldt_2), time (ldt_1)) / 3600.0

return ll_hours

You may have to experiment to figure out which inputs to use in which order.

Paul

P.S.:  Could SOME PERSON WHO ACTS AS MODERATOR AND USUALLY TAKES DELIGHT IN DELETING MY POST PLEASE RESTORE MY ACCOUNT WITH EMAIL ADDRESS pemurray@interrasys.com SO IT IS NO LONGER ASSOCIATED WITH SOMEONE WITH INTIALS T.R.?

10|10000 characters needed characters exceeded
• Former Member

Thanks Paul.

It works..need some fine tuning though..

Thanks again

• Former Member
Dec 30, 2014 at 10:04 PM

Good Olde PFC has a function:

//////////////////////////////////////////////////////////////////////////////

//

// Function:   of_SecondsAfter

//

// Access:   public

//

// Arguments:

//

// Returns:   long

// Number of whole seconds between two date times.

// If any argument's value is NULL, function returns NULL.

// If any argument's value is Invalid, function returns NULL.

//

// Description:   Given two datetimes, return the number of seconds between

// the two.

//

//////////////////////////////////////////////////////////////////////////////

//

// Revision History

//

// Version

// 5.0   Initial version

//

//////////////////////////////////////////////////////////////////////////////

//

/*

* Open Source PowerBuilder Foundation Class Libraries

*

*

* Redistribution and use in source and binary forms, with or without

* modification, are permitted in accordance with the GNU Lesser General

* Public License Version 2.1, February 1999

*

*

* ====================================================================

*

* This software consists of voluntary contributions made by many

* individuals and was originally based on software copyright (c)

* 1996-2004 Sybase, Inc. http://www.sybase.com.  For more

* information on the Open Source PowerBuilder Foundation Class

* Libraries see http://pfc.codexchange.sybase.com

*/

//

//////////////////////////////////////////////////////////////////////////////

date ld_sdate, ld_edate

time lt_stime, lt_etime

//Check parameters

long ll_null

SetNull(ll_null)

Return ll_null

End If

//Note: 86400 is number of seconds in a day.

If ld_sdate = ld_edate then

ll_total_seconds = secondsafter( lt_stime,lt_etime)

Elseif ld_sdate < ld_edate Then

ll_total_seconds = SecondsAfter(lt_stime,Time('23:59:59'))

If ll_day_adjust > 0 Then ll_total_seconds = ll_total_seconds + 86400 * ll_day_adjust

ll_total_seconds = ll_total_seconds + SecondsAfter(Time('00:00:00'),lt_etime) +1

Else //end date < start date

ll_total_seconds = SecondsAfter(lt_stime,Time('00:00:00'))

If ll_day_adjust < 0 Then ll_total_seconds = ll_total_seconds + 86400 * ll_day_adjust

ll_total_seconds = ll_total_seconds + SecondsAfter(Time('23:59:59'),lt_etime) -1

end If

return ll_total_seconds

10|10000 characters needed characters exceeded
• Former Member
Dec 30, 2014 at 12:22 PM

Depending upon your situation it may be easier to let the database handle these sorts of things.  In MS SQLserver you can do something like

sqlstatement = 'select DateDiff(mi, "' + string(ldt_start) + '", "' + string(ldt_stop) + '")'PREPARE SQLSA FROM :sqlstatement ;OPEN DYNAMIC get_time_diff ;FETCH get_time_diff INTO :ll_dur_qy ;CLOSE get_time_diff ;

You can change the time difference designator ('mi' in the above example) to seconds, days, weeks, etc.

In any event you may need to handle 'fat fingers' in your input prior to obtaining the date difference. (someone enters 1014 as a start year instead of 2014 for example).