|
|
|||||||
We have been using an application that I developed several years ago that does some math with a given date, adds a given number of days and displays the results. Some days ago we were audited by one of our suppliers and they reported an inconsistency in this date math. Case: When I feed a date before Feb 29th to the date math section and add some days I get the correct date. When I feed a date after Feb 29th I get one extra day. I tried this with leap years and "regular" years. Date math has always been a bit of hocus pocus to me and I've never been able to script that without using one of the available UDFs. Looking at the results of the TimeDiff UDF I suspect it has something to do with leap years but I'm not sure. Can someone shed some light on this? Code: Break on $startdate = "2016/02/28" ? "Start date: " + $startdate $increase = 3 ? "Add " + $increase + " days" $enddate = DateCalc($startdate, $increase) ? "End date: " + $enddate $days = TimeDiff($startdate, $enddate, "d") ? "Difference in days: " + $days Sleep 10 ;FUNCTION DateCalc() ; ;AUTHOR Jochen Polster (jochenDOTpolsterATgmxDOTnet) ; based on date algorithms by Peter Baum to be found here : ; http://www.capecod.net/~pbaum/date/date0.htm ; ;VERSION 1.12 ; ;VERSION HISTORY 1.0 2001/12/10 Initial release ; ; 1.1 2004/02/18 Added support for single digit month/day input ; and optional single digit month/day date return ; ; 1.11 2004/02/20 Minor Variable handling fix ; ; 1.12 2005/03/31 Finally supports "NoVarsInStrings" and "Explicit" set to "ON" in ; all possible variations ; ;ACTION Calculates days between 2 dates or returns a date string calculated from ; a given date and a given amount of days ( Addition of positive or negative ; integer value ) ; ;SYNTAX DateCalc( Date1, Date2|Modifier, [SingleDigit] ) ; ;PARAMETERS Date1 (Required) ; - (Gregorian) Date string in Format : YYYY/M[M]/D[D] ; ; Date2|Modifier (Required) ; - either a second (Gregorian) date string (YYYY/M[M]/D[D]) to calculate days between ; or a positive/negative amount of days to calculate with ; ; SingleDigit (Optional) ; - if not zero date will be returned unpadded, eg. 2004/2/9 ; ;REMARKS Date format must be KiX friendly : YYYY/M[M]/D[D] (2001/11/20) ; To calculate a date less than given assign a negative integer (ie. -45 ) ; ;RETURNS Either a positive integer value of days between two given dates, ; or a (Gregorian) date string. ; ;DEPENDENCIES None ! ; ;EXAMPLES ; break on ; call "[path]DateCalc.udf" ; ; "boot.ini last modified : " + DateCalc(@date,substr(getfiletime("c:\boot.ini"),1,10)) ; + " days ago ..." ? ? ; ; $mod = 60 ; "in/before $mod day(s) it was/will be " + DateCalc(@date,$mod) ? ? ; ; get $ Function DateCalc($date1, $DateOrMod, optional $SingleDigit) Dim $_intDate1, $_intYear1, $_intMonth1, $_intDay1 Dim $_intDate2, $_intYear2, $_intMonth2, $_intDay2 $date1 = Split($date1, '/') If UBound($date1) <> 2 Exit 1 EndIf $_intYear1 = Val($date1[0]) $_intMonth1 = Val($date1[1]) $_intDay1 = Val($date1[2]) If $_intMonth1 < 3 $_intMonth1 = $_intMonth1 + 12 $_intYear1 = $_intYear1 - 1 EndIf $_intDate1 = $_intDay1 + (153 * $_intMonth1 - 457) /5 + 365 * $_intYear1 + $_intYear1 /4 - $_intYear1 /100 + $_intYear1 /400 - 306 Select Case VarType($DateOrMod) = 3 $_intDate2 = $_intDate1 + $DateOrMod If InStr($_intDate2, '-') $_intDate2 = Val(SubStr($_intDate2, 2, Len($_intDate2) - 1)) EndIf $_intYear2 = ( 100 * ( ((100 * ($_intDate2 + 306) - 25) /3652425) - (((100 * ($_intDate2 + 306) - 25) /3652425) /4) ) + (100 * ($_intDate2 + 306) - 25) ) /36525 $_intMonth2 = ( 5 * ( ((100 * ($_intDate2 + 306) - 25) /3652425) - (((100 * ($_intDate2 + 306) - 25) /3652425) /4) + ($_intDate2 + 306) - 365 * $_intYear2 - $_intYear2 /4 ) + 456 ) /153 $_intDay2 = ( ((100 * ($_intDate2 + 306) - 25) /3652425) - (((100 * ($_intDate2 + 306) - 25) /3652425) /4) + ($_intDate2 + 306) - 365 * $_intYear2 - $_intYear2 /4 ) - ( 153 * $_intMonth2 - 457 ) /5 If $_intMonth2 > 12 $_intYear2 = $_intYear2 + 1 $_intMonth2 = $_intMonth2 - 12 EndIf If Not $SingleDigit If Len($_intYear2) < 4 $_ = Execute("for $i=1 to 4-len($$_intYear2) $$_intYear2 = '0' + $$_intYear2 next") EndIf $_intMonth2 = Right("0" + $_intMonth2, 2) $_intDay2 = Right("0" + $_intDay2, 2) EndIf $DateCalc = '' + $_intYear2 + '/' + $_intMonth2 + '/' + $_intDay2 Case VarType($DateOrMod) = 8 $DateOrMod = Split($DateOrMod, '/') If UBound($DateOrMod) <> 2 Exit 1 EndIf $_intYear2 = Val($DateOrMod[0]) $_intMonth2 = Val($DateOrMod[1]) $_intDay2 = Val($DateOrMod[2]) If $_intMonth2 < 3 $_intMonth2 = $_intMonth2 + 12 $_intYear2 = $_intYear2 - 1 EndIf $_intDate2 = $_intDay2 + (153 * $_intMonth2 - 457) /5 + 365 * $_intYear2 + $_intYear2 /4 - $_intYear2 /100 + $_intYear2 /400 - 306 $DateCalc = $_intDate1 - $_intDate2 ;comment the next line if you wish to return negative results also !!! If InStr($DateCalc, '-') $DateCalc = Val(SubStr($DateCalc, 2, Len($DateCalc) - 1)) EndIf Case 1 Exit 1 EndSelect EndFunction ;FUNCTION TimeDiff() ; ;AUTHOR Glenn Barnas ; ;VERSION 2.2a / 2007/10/17 ; Modified to increase accuracy, permit fracional second calculations ; 2.1 / 2007/03/17 ; added "now" and "today" options for both start and end times ; 2.0 / 2006/11/20 ; Changes for code efficiency; added defaults for midnight ; ;ACTION Calculates the time difference between two given date/time strings ; ;SYNTAX TimeDiff(Start [, End] [, Format] [, MSec]) ; ;PARAMETERS Start - REQUIRED, String value representing the start timestamp ; Format yyyy/mm/dd hh:mm:ss ; ; End - OPTIONAL, Defaults to "now" ; String value representing the ending time ; Format yyyy/mm/dd hh:mm:ss ; Can be the special value "now" for the current date/time, or "today" ; for midnight of the current day. ; ; When the time value is not specified, it defaults to 00:00:00.000 (midnight) ; ; Format - OPTIONAL, one of: ; "m" - return minutes ; "h" - return hours ; "d" - return days ; "y" - Return years ; When a format value is specified, it returns the fractional part (ie 0.5 days for 12 hours). ; ; MSec - OPTIONAL, True if the fractional seconds should be returned. Default ; is false, returning whole seconds, to maintain compatibility with earlier versions. ; MSec only affects the return of fractional seconds, not fractional parts of other time formats. ; ;REMARKS Returns a value representing the difference in time between two date/time ; strings. Assumes that "Start" is in the past, but will properly return a ; negative value if it is in the future. ; ;RETURNS Double - difference between Start and End timestamps in seconds ; ;DEPENDENCIES None ; ;TESTED WITH Kix 4.2+, NT4, W2K, WXP, W2K3 ; ;EXAMPLES If TimeDiff(GetFileTime('SomeFile.txt'), 'now', 'h') > 48 ; "File is more than 2 days old!" ? ; EndIf Function TimeDiff($_Start, OPTIONAL $_End, OPTIONAL $_Fmt, OPTIONAL $_MSec) Dim $_, $_SDate, $a_Start, $_EDate, $a_End, $_Duration ; Check for special START parameters Select Case $_Start = 'now' $_Start = @DATE + ' ' + @TIME + '.' + @MSECS Case $_START = 'today' $_Start = @DATE + ' 00:00:00.000' EndSelect ; Check for special END parameters Select Case $_End = 'now' Or $_End = '' $_End = @DATE + ' ' + @TIME + '.' + @MSECS Case $_End = 'today' $_End = @DATE + ' 00:00:00.000' EndSelect ; Validate parameters ; Parameters passed are "yyyy/mm/dd hh:mm:ss[.sss]" - make sure the default time is added $a_Start = Split(Join(Split(Join(Split($_Start + ' 00:00:00.000', '/'), ' '), ':'), ' '), ' ', 6) If UBound($a_Start) <> 5 Exit 87 EndIf ; bad start time parameter For $_ = 0 to 5 $a_Start[$_] = CDbl($a_Start[$_]) ; convert to numeric values Next $a_End = Split(Join(Split(Join(Split($_End + ' 00:00:00.000', '/'), ' '), ':'), ' '), ' ', 6) If UBound($a_End) <> 5 Exit 87 EndIf ; bad start time parameter For $_ = 0 to 5 $a_End[$_] = CDbl($a_End[$_]) ; convert to numeric values Next ; Convert dates to Days, then convert to seconds and add the time value If $a_Start[1] < 3 $a_Start[1] = $a_Start[1] +12 $a_Start[0] = $a_Start[0] -1 EndIf $_SDate = $a_Start[2] + (153 * $a_Start[1] -457) /5 + 365 * $a_Start[0] + $a_Start[0] /4 - $a_Start[0] /100 + $a_Start[0] /400 - 306 $_SDate = CDbl($_SDate) * 86400.0 $_SDate = $_SDate + $a_Start[3] * 3600 + $a_Start[4] * 60 + $a_Start[5] If $a_End[1] < 3 $a_End[1] = $a_End[1] +12 $a_End[0] = $a_End[0] -1 EndIf $_EDate = $a_End[2] + (153 * $a_End[1] -457) /5 + 365 * $a_End[0] + $a_End[0] /4 - $a_End[0] /100 + $a_End[0] /400 - 306 $_EDate = CDbl($_EDate) * 86400.0 $_EDate = $_EDate + $a_End[3] * 3600 + $a_End[4] * 60 + $a_End[5] ; Get the duration between the timestamps $_Duration = CDbl($_EDate - $_SDate) ; Return data as a Double - seconds (default), hours, minutes, days, or years Select Case $_Fmt = 'm' ; minutes $TimeDiff = $_Duration /60.0 Case $_Fmt = 'h' ; hours $TimeDiff = $_Duration /3600.0 Case $_Fmt = 'd' ; days $TimeDiff = $_Duration /86400.0 Case $_Fmt = 'y' ; years $TimeDiff = $_Duration /31536000.0 Case 1 ; Trim fractional seconds if the MSec flag wasn't set - Value returned is whole seconds If Not $_MSec $_Duration = CDbl(Split(CStr($_Duration), '.')[0]) EndIf $TimeDiff = $_Duration EndSelect Exit 0 EndFunction |
||||||||
|
|
|||||||
The TimeDiff and TimeConvert UDFs are derived directly from the published Rata Die algorithms. In a way, the value of 2.2425 "sort of" makes sense, because each year is 365.2425 days long, but the fractional part is discarded for 3 years and then added to Feb in the 4th year. Testing with TimeConvert returns the same value when converting 2/28 to a cTime, adding (3.0*86400) and converting back to a date. I get 3/2 (correct) but TimeDiff returns 2.2454 days - seemingly not properly accumulating leap year fractions. I'll take a look at the function. in the mean time, try TimeConvert Code: $enddate = TimeConvert(TimeConvert($startdate) + (3 * 86400)) Glenn |
||||||||
|
|
|||||||
... uh oh, you two let me know if DateCalc() has a bug, will you? |