With the Timestamp command introduced in v16R2, it is easier to work with stored date and time data. Below is an example of utilizing the new command to store start and end times and calculating the difference, which can be a common need when storing time data.

Below is a utility method that will calculate the time difference between two ISO format times including the difference generated between days.

// Util_SQL_TIMEDIFF // // Desription: // Used For SQL calculation of Diffiference between two times // // Parameters: // $1: End Time (Larger Value/More Recent Date of the Two) // $2: Start Time (Lesser Value/Less Recent Date of the Two) // Result: // $0: Time difference between end and start times in hours:minutes:seconds // ---------------------------------------------------- C_TEXT($1;$end_Tm) C_TEXT($2;$start_Tm) C_TIME($0;$timeDiff_tm) If (Count parameters=2)     $end_Tm:=$1     $start_Tm:=$2         $timeDiff_tm:=(24*?01:00:00?)*(Date($end_Tm)-Date($start_Tm))     $timeDiff_tm:=$timeDiff_tm+Time($end_Tm)-Time($start_Tm)     $0:=$timeDiff_tm End if

The method can also be used in SQL calls by first allowing it to be used with SQL in the Method Properties.


Then with the following sample table:


The following is an example of use:

ARRAY TIME(arrTimes;0) Begin SQL     SELECT {fn Util_ISOTimeDiff(End_Time,Start_Time) AS DURATION}     From Test     where ID>0     into :arrTimes End SQL

Which results in the following:


As shown the difference in the first record is about 6-7 hours and the second record is about 684-685 hours which is about 28-29 days when dividing by 24 hours.