[RBASE-L] - Re: DateTime tutorial

[Jason Kramer]

I have another possibility if anyone is interested: -- 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 retunred. -- 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 elpased 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 = 1 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/17/2012 4:20 PM, Albert Berry wrote: For anyone who would like to put this into a stored procedure, or test the math, here is my take on this. For a stored procedure you would need to change the "sho var" at the bottom, of course. If you remove the two datetime values, you can pass any two datetime values to the system.