FINDING DATETIME DIFFERENCE

[Former Member]

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

[Former Member · Accepted solution]

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.? 

[Former Member · Accepted solution]

Thanks Paul.

It works..need some fine tuning though..

Thanks again

[Former Member]

Good Olde PFC has a function:

//////////////////////////////////////////////////////////////////////////////
//
// Function:   of_SecondsAfter
//
// Access:   public
//
// Arguments:
// adtm_start Beginning time.
// adtm_end   Ending time.
//
// 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
 *
 * Copyright (c) 2004-2005, All rights reserved.
 *
 * 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
 *
 * http://www.gnu.org/copyleft/lesser.html
 *
 * ====================================================================
 *
 * 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
*/
//
//////////////////////////////////////////////////////////////////////////////


long ll_total_seconds, ll_day_adjust
date ld_sdate, ld_edate
time lt_stime, lt_etime


//Check parameters
If IsNull(adtm_start) or IsNull(adtm_end) or &
  Not of_IsValid(adtm_start) or Not of_IsValid(adtm_end) Then
  long ll_null
  SetNull(ll_null)
  Return ll_null
End If


ld_sdate = date(adtm_start)
ld_edate = date(adtm_end)


lt_stime = time(adtm_start)
lt_etime = time(adtm_end)


//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'))
  ll_day_adjust = DaysAfter(ld_sdate,ld_edate) -1
  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'))
  ll_day_adjust = DaysAfter(ld_sdate,ld_edate) +1
  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

[Former Member]

I don't understand why PowerBuilder can't have a simple built in function for this. It's a big pain to have to some sort of grand calculation to get the difference between two datetime values. Hitting the database just to do that is also annoying.Oh well, one can only dream...

[Former Member]

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