Page 1 of 1 1
Topic Options
#212069 - 2016-11-03 09:19 PM Date math - looking for confirmation and/or explanation
Mart Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4672
Loc: The Netherlands
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
_________________________
Mart

- Chuck Norris once sold ebay to ebay on ebay.

Top
#212072 - 2016-11-03 10:05 PM Re: Date math - looking for confirmation and/or explanation [Re: Mart]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
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
_________________________
Actually I am a Rocket Scientist! \:D

Top
#212117 - 2016-11-15 04:28 PM Re: Date math - looking for confirmation and/or explanation [Re: Glenn Barnas]
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
... uh oh,
you two let me know if DateCalc() has a bug, will you?
_________________________



Top
Page 1 of 1 1


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 466 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.05 seconds in which 0.023 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org