[RBASE-L] - Re: Date Time Difference Procedure - BUG FIXES

[Jason Kramer]

I discovered a bug in the code I posed to the list yesterday too.  I'll paste the correctde code below.  I had an '= 1' where I should have had a '<> 0'.                                                                             Jason -- DTD.RMD -- Jason Kramer -- 10-18-2012 -- This RMD file is used by the DTD stored procedure.  It generates the -- time difference between two DATETIME values.  It returns the difference -- as a TEXT string.  The DATETIME values don't have to be in order, if the -- end time is before the start time, then negative results will be returned. -- Milliseconds are always returned, even if the current TIME FORMAT does not -- use them (in which case a 0 will be returned for milliseconds). -- Required parameter - vdtdstart DATETIME - The start date & time. -- Required parameter - vdtdend DATETIME - The end date & time. -- Return value - The elapased time in a string formatted as follows: --  years,months,days,hours,minutes,seconds,milliseconds SET VAR vdtdstartd DATE = NULL SET VAR vdtdendd DATE = NULL SET VAR vdtddaydif INTEGER = NULL SET VAR vdtdstartt TIME = NULL SET VAR vdtdendt TIME = NULL SET VAR vdtdtimedif INTEGER = NULL SET VAR vdtdtformat TEXT = NULL SET VAR vdtdsubsec INTEGER = NULL SET VAR vdtddir INTEGER = NULL SET VAR vdtdswap DATETIME = NULL SET VAR vdtdyrs INTEGER = NULL SET VAR vdtdmos INTEGER = NULL SET VAR vdtddays INTEGER = NULL SET VAR vdtdhours INTEGER = NULL SET VAR vdtdmins INTEGER = NULL SET VAR vdtdsec INTEGER = NULL SET VAR vdtdthouths INTEGER = NULL SET VAR vdtdretval TEXT = NULL -- Determine if the current TIME FORMAT uses milliseconds. SET VAR vdtdtformat = (LUC(CVAL('TIME'))) SET VAR vdtdsubsec = (SLOC(.vdtdtformat,'.')) -- Adjust for time past vs. time remaining. IF vdtdstart > .vdtdend THEN   SET VAR vdtddir = -1   SET VAR vdtdswap = .vdtdstart   SET VAR vdtdstart = .vdtdend   SET VAR vdtdend = .vdtdswap ELSE   SET VAR vdtddir = 1 ENDIF -- Separate the DATE and TIME components. SET VAR vdtdstartd = (DEXTRACT(.vdtdstart)) SET VAR vdtdendd = (DEXTRACT(.vdtdend)) SET VAR vdtdstartt = (TEXTRACT(.vdtdstart)) SET VAR vdtdendt = (TEXTRACT(.vdtdend)) SET VAR vdtddaydif = .vdtdendd - .vdtdstartd SET VAR vdtdtimedif = .vdtdendt - .vdtdstartt -- Adjust the number of days if the TIME portion of the elapsed time is less -- that a full day. IF vdtdtimedif < 0 THEN   SET VAR vdtddaydif = .vdtddaydif - 1   IF vdtdsubsec <> 0 THEN     SET VAR vdtdtimedif = 86400000 + .vdtdtimedif   ELSE     SET VAR vdtdtimedif = 86400 + .vdtdtimedif   ENDIF ENDIF -- Calculate the hours, minutes, seconds, and milliseconds. IF vdtdsubsec <> 0 THEN   SET VAR vdtdhours = (INT(.vdtdtimedif / (60 * 60 * 1000)))   SET VAR vdtdmins = (INT((.vdtdtimedif - (.vdtdhours * 60 * 60 * 1000)) / (60 * 1000)))   SET VAR vdtdsec = (INT((.vdtdtimedif - (.vdtdhours * 60 * 60 * 1000) - (.vdtdmins * 60 * 1000)) / 1000))   SET VAR vdtdthouths = .vdtdtimedif - (.vdtdhours * 60 * 60 * 1000) - (.vdtdmins * 60 * 1000) - (.vdtdsec * 1000) ELSE   SET VAR vdtdhours = (INT(.vdtdtimedif / (60 * 60)))   SET VAR vdtdmins = (INT((.vdtdtimedif - (.vdtdhours * 60 * 60)) / (60)))   SET VAR vdtdsec = .vdtdtimedif - (.vdtdhours * 60 * 60) - (.vdtdmins * 60)   SET VAR vdtdthouths = 0 ENDIF -- Calculate the years, months, and days. SET VAR vdtdyrs = (INT(.vdtddaydif / 365.25)) SET VAR vdtdmos = (INT((.vdtddaydif - (.vdtdyrs * 365.25)) / 30.4375)) SET VAR vdtddays = .vdtddaydif - (.vdtdyrs * 365.25) - (.vdtdmos * 30.4375) -- If the time elapsed is for the time SINCE an event occured, negate the -- values. IF vdtddir = -1 THEN   SET VAR vdtdyrs = .vdtdyrs * -1   SET VAR vdtdmos = .vdtdmos * -1   SET VAR vdtddays = .vdtddays * -1   SET VAR vdtdhours = .vdtdhours * -1   SET VAR vdtdmins = .vdtdmins * -1   SET VAR vdtdsec = .vdtdsec * -1   SET VAR vdtdthouths = .vdtdthouths * -1 ENDIF SET VAR vdtdretval = (CTXT(.vdtdyrs)) + ',' + (CTXT(.vdtdmos)) + ',' + (CTXT(.vdtddays)) + ',' + (CTXT(.vdtdhours)) + ',' + (CTXT(.vdtdmins)) + ',' + (CTXT(.vdtdsec)) + ',' + (CTXT(.vdtdthouths)) CLEAR VAR vdtdstart,vdtdend,vdtdstartd,vdtdendd,vdtddaydif,vdtdstartt,vdtdendt CLEAR VAR vdtdtimedif,vdtdtformat,vdtdsubsec,vdtddir,vdtdswap,vdtdyrs,vdtdmos CLEAR VAR vdtddays,vdtdhours,vdtdmins,vdtdsec,vdtdthouths RETURN .vdtdretval Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) On 10/18/2012 6:11 PM, Albert Berry wrote: Thanks for the bug report, Jason. Here is the modified procedure. 1. Modified to ignore AM/AP time formats when checking for thousandths of a second. 2. Modified to allow more than 23 days when seconds are in thousandths (an integer difference of 1,999,999,999 is 23 days, 3 hours, 33 minutes and 20 seconds). As modified, I tested it to 100 years and it passed. ------------------------------------------------------------------------------ -- DateDiff.pro -- Returns the difference between two datetime entries -- in hours, minutes and seconds -- Albert Berry 2012/10/17 ------------------------------------------------------------------------------ { BUG FIXES 20122/10/18 1. Jason Kramer reported that the procedure fails when the time format    includes the AM/PM parameter.    FIX: Modified procedure to check for the string ".S" not the length of         the time format.    Thanks, Jason! 2. Found by me, testing for Jason's bug find.    If the time setting includes fractions of a second, an integer value does    not have enough digits to handle long stretches of time - it fails before    one year has passed between the two date times and returns 0 as if both    values were the same..    FIX: I have modified the procedure to use a DOUBLE variable for the         difference, and it tests OK to 100 years plus. I have not attempted         to calculate the years from the days because of leap years. } ------------------------------------------------------------------------------ SET VAR vDateTime1 DATETIME SET VAR vDateTime2 DATETIME -- output variable SET VAR vDateTimeDiff TEXT = NULL -- working variables CLEAR VAR vDifference, vHours, vDays, vMinutes, vSeconds SET VAR vDifference DOUBLE = (.vDateTime2 - .vDateTime1) SET VAR vTimeSetting TEXT = (CVAL("TIME")) ------------------------------------------------------------------------------ -- Checks for the existence of a fractional component to the time setting -- and rounds to the nearest second if so. ------------------------------------------------------------------------------ IF vTimeSetting LIKE "%.S%" THEN     SET VAR vDifference = (NINT(.vDifference/1000)) -- round to nearest second ENDIF ------------------------------------------------------------------------------ SET VAR vDays INTEGER = (INT(.vDifference/86400)) SET VAR vDifference2 DOUBLE = (.vDifference - (.vDays * 86400)) SET VAR vHours INTEGER = (INT(.vDifference2 / 3600)) SET VAR vDifference3 DOUBLE = (.vDifference2 - (.vHours * 3600)) SET VAR vMinutes INTEGER = (INT(.vDifference3 / 60)) SET VAR vDifference4 DOUBLE = (.vDifference3 - (.vMinutes * 60)) SET VAR vSeconds INTEGER = (NINT(.vDifference4)) -- NINT rounds SET VAR vDateTimeDiff = ( (CTXT(.vDays)) + "," + (CTXT(.vHours)) + +     "," + (CTXT(.vMinutes)) + "," + (CTXT(.vSeconds))) ------------------------------------------------------------------------------ LABEL Stop SHOW VAR v% RETURN