Page 2 of 2 <12
Topic Options
#147183 - 2006-10-04 10:07 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Now you know where I grabbed the header!
Top
#147184 - 2006-10-04 10:08 PM Re: RFC: fnDateDiff() - Perform date calculations
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
Nice catch.
Top
#147185 - 2006-10-06 04:49 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Ok. Here is a new version that uses a different algorithm. If DATE1 is the only parameter provided it will return the Chronological Julian Day Number if, for some reason, you need that.

Code:
; 
;Function:
; fnDateDiff()
;
;Author:
; Christopher Shilt (gk_zone@hotmail.com)
;
;Contributors:
; Jochen Polster (jochenDOTpolsterATgmxDOTnet)
;
;Version:
; 1.0 (October 3, 2006)
;
;Version History:
;
;Action:
; Calculates a specified interval between two dates or returns the date/time difference
; of a specified interval.
;
;Syntax:
; fnDateDiff(DATE1, [DATE2|INTEGER], [INTERVAL], [FIX])
;
;Parameters:
; DATE1 : Required. Gregorian datetime in YYYY/M[M]/D[D] [HH:MM:SS] format.
; Note: Time is not required, if omitted 00:00:00 (midnight) will be
; used.
;
; DATE2|INTERVAL : Optional. Gregorian datetime in YYYY/M[M]/D[D] [HH:MM:SS] format or
; positive or negative integer to add to DATE1.
;
; INTERVAL : Optional. Type of interval to add to DATE1. Supported formats are:
;
; S : Seconds
; M : Minutes
; H : Hours
; D : Days
; W : Weeks
;
; FIX : Optional. Removes the fractional part of number and returns the
; resulting integer value.
;
;Remarks:
;
; Based on date algorithms by Henry F. Fliegel and Thomas C. Van Flandern
; (http://hermetic.nofadz.com/cal_stud/jdn.htm#comp)
;
;Returns:
;
; The Chronological Julian Day Number of DATE1 if no other parameter is used.
;
; The Datetime string of an interval added to DATE1 if an INTERVAL is the second parameter.
;
; The difference (positive or negative) between two given datetimes if DATE2 is the second
; parameter.
;
; Sets the value of @ERROR based on success/failure.
;
;Dependencies:
;
;
;Example:
;
; "One second after midnight: " + fnDateDiff(@DATE,1,"s") ?
; "One second before midnight: " + fnDateDiff(@DATE,-1,"s") ?
; "Seconds between today and tomorrow: " + fnDateDiff(@DATE,fnDateDiff(@DATE,1,"d"),"s",0) ?
; "Weeks between today and six days from today: " + fnDateDiff(@DATE,fnDateDiff(@DATE,6,"d"),"w",0) ?
; "Weeks (fixed) between today and six days from today: " + fnDateDiff(@DATE,fnDateDiff(@DATE,6,"d"),"w",1) ?
; "Shopping days until my birthday: " + fnDateDiff(@DATE,"2007/04/20","d") ?
; "Seconds between 1/1/1601 and 1/1/1970: " + fnDateDiff("1601/01/01","1970/01/01","s") ?
; "Hours between NOW and 5:00PM today: " + fnDateDiff(@DATE+' '+@TIME,@DATE+' '+"17:00:00","h") ?
; "Hours between 5:00PM today and NOW: " + fnDateDiff(@DATE+' '+"17:00:00",@DATE+' '+@TIME,"h") ?
; "Yesterday's date: " + fnDateDiff(@DATE,-1,"d") ?
; "Seconds between midnight and 11:59:59 pm: " + fnDateDiff("00:00:00","23:59:59","s") ?
; "Seconds between 11:59:59 pm and midnight: " + fnDateDiff("23:59:59","00:00:00","s") ?
; "Add one minute to midnight: " + fnDateDiff("00:00:00",1,"m",0) ?
; "VarTypeName of fnDateDiff output: " + VarTypeName(fnDateDiff(@TIME,0,"s")) ?
; "VarTypeName of @@TIME: " + VarTypeName(@TIME) ?
; "The Chronological Julian Day Number of 2003/11/08 18:00:00 is: " + fnDateDiff("2003/11/08 18:00:00") ?
; "Today is: "
; $weekday = fnDateDiff(@DATE) mod 7
; Select
; Case $weekday = 0 "Monday" ?
; Case $weekday = 1 "Tuesday" ?
; Case $weekday = 2 "Wednesday" ?
; Case $weekday = 3 "Thursday" ?
; Case $weekday = 4 "Friday" ?
; Case $weekday = 5 "Saturday" ?
; Case $weekday = 6 "Sunday" ?
; EndSelect
; @ERROR " | " @SERROR ?
; ? "Press any key to continue." Get $
;

Function fnDateDiff($DateTime,Optional $DateOrInterval,$Interval,$Fix)
Dim $Date1,$Date2,$jDate[2],$jTime[2],$y,$m,$d,$ss,$mm,$hh,$i,$l,$n,$j
$Date1=Split($DateTime," ")
If UBound($Date1)>1 Exit 1901 EndIf
If UBound($Date1)=0
Select
Case InStr($Date1[0],"/") $jDate=Split($Date1[0],"/")
Case InStr($Date1[0],":")
$jDate=Split("1899/12/30","/")
$jTime=Split($Date1[0],":")
Case 1 Exit 1901
EndSelect
Else
If InStr($Date1[0],"/") and InStr($Date1[1],":")
$jDate=Split($Date1[0],"/")
$jTime=Split($Date1[1],":")
Else
Exit 1901
EndIf
EndIf
$y=Val($jDate[0]) If $y=0 Exit 1901 EndIf
$m=Val($jDate[1]) If $m<1 or $m>12 Exit 1901 EndIf
$d=Val($jDate[2]) If $d<1 or $d>31 Exit 1901 EndIf
$jDate = CDbl((1461*($y+4800+($m-14)/12))/4+(367*($m-2-12*(($m-14)/12)))/12-
(3*(($y+4900+($m-14)/12)/100))/4+$d-32075)
$jTime = CDbl((Val($jTime[0])*3600)+(Val($jTime[1])*60)+Val($jTime[2]))/86400
If $jTime=>1 Exit 1901 EndIf
$Date1=CDbl($jDate+$jTime)

If VarType($DateOrInterval)
If InStr($DateOrInterval,"/") or InStr($DateOrInterval,":")
$Date2=fnDateDiff($DateOrInterval)
$fnDateDiff=($Date2-$Date1)*86400
Select
Case $Interval="s"
Case $Interval="m" $fnDateDiff=$fnDateDiff/60
Case $Interval="h" $fnDateDiff=$fnDateDiff/3600
Case $Interval="d" $fnDateDiff=$fnDateDiff/86400
Case $Interval="w" $fnDateDiff=$fnDateDiff/604800
Case 1 Exit 87
EndSelect
If $Interval="s" $fnDateDiff=CInt($fnDateDiff) EndIf
If $Fix $fnDateDiff=Fix($fnDateDiff) EndIf
Else
If VarType($DateOrInterval)>5 Exit 87 EndIf
Select
Case $Interval="s" If VarType($DateOrInterval)>3 Exit 87 EndIf
Case $Interval="m" $DateOrInterval=$DateOrInterval*60
Case $Interval="h" $DateOrInterval=$DateOrInterval*3600
Case $Interval="d" $DateOrInterval=$DateOrInterval*86400
Case $Interval="w" $DateOrInterval=$DateOrInterval*604800
Case 1 Exit 87
EndSelect

$fnDateDiff=$Date1+($DateOrInterval/86400)
$jTime=$fnDateDiff-Fix($fnDateDiff)
$jDate=Fix($fnDateDiff)

$l = $jDate+68569 $n = (4*$l)/146097 $l = $l-(146097*$n+3)/4
$i = (4000*($l+1))/1461001 $l = $l-(1461*$i)/4+31 $j = (80*$l)/2447
$d = $l-(2447*$j)/80 $l = $j/11 $m = $j+2-(12*l) $y = 100*($n-49)+$i+$l
If $m>12 $m=$m-12 EndIf
If $y<0 $y=$y*-1 $y=Right("-000"+$y,5) Else $y = Right("000"+$y,4) EndIf
$m = Right("0"+$m,2)
$d = Right("0"+$d,2)

$ss = CInt(86400.0*$jTime)
$mm=$ss/60 $ss=$ss mod 60 $hh=$mm/60 $mm=$mm mod 60

$jDate=Iif($y,''+$y+'/'+$m+'/'+$d,'')
$jDate=Iif($y=1899 & $m=12 & $d=30,'',$jDate)
$jTime=Iif($jTime,Right("0"+$hh,2)+':'+Right("0"+$mm,2)+':'+Right("0"+$ss,2),'')

$fnDateDiff=Iif($jDate & $jTime,$jDate+" "+$jTime,$jDate+$jTime)
EndIf
Else
$fnDateDiff=$Date1
EndIf
EndFunction


Top
#147186 - 2006-10-10 09:22 PM Re: RFC: fnDateDiff() - Perform date calculations
masken Offline
MM club member
*****

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
hmm... perhaps a little buggie. Try comparing something with 1 sec in difference and report it as days.... did that "by mistake", lol (but could also be a real case when comparing stuff in a script):

$Date1 = "2006-10-01 20:06:22"
$Date2 = "2006-10-01 20:06:23"

fnDateDiff($Date1, $Date2, "d")


Edited by masken (2006-10-10 09:25 PM)

Top
#147187 - 2006-10-10 09:53 PM Re: RFC: fnDateDiff() - Perform date calculations
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
What did it report? I'd think it should have reported 0
Top
#147188 - 2006-10-10 11:12 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Here it is in a nutshell...

CJD of "2006/10/01 20:06:22" = 454010.83775463

CJD of "2006/10/01 20:06:23" = 454010.8377662

The difference (Date2-Date1) should be 0.00001157, but KiXtart returns 1.15740112960339E-005.

The difference of Date2 and Date1 multiplied by 86400 = 0.999994575977325

When divided by 86400 we get the same result as above, 1.15740112960339E-005.

Maybe Richard can explain why this math doesn't work with KiXtart, but I imagine it has something to do with 32 bit integers.

Top
#147189 - 2006-10-11 01:20 PM Re: RFC: fnDateDiff() - Perform date calculations
masken Offline
MM club member
*****

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
yeah that is my own novice guess too.

Another thing that perhaps should be in the date-check is a:
JOIN(SPLIT($date, "-"), "/"), since dates are often reported by other apps, windows etc with hyphens (ISO-standard) instead of slashes (KiX-"standard"), as exampled above.


Edited by masken (2006-10-11 01:22 PM)

Top
#147190 - 2006-10-11 10:20 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Well, many of the other date functions require KiXtart format for date, so I don't think it unreasonable to require it here also. If you want a function that is extremely forgiving with the date format, see the first version of fnDateDiff() in this thread. It uses the WbemScripting.SWbemDateTime date object and accepts virtually any valid date format and returns date using your localized short date format.

As far as the reported "bug", I'd say that it is not a bug since it is a limitation of KiX itself. If the interval between times is expected to be short, one should use "s" for the interval type.

Top
#147191 - 2006-10-11 10:26 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
For fun, try...

Code:

$Date1 = "2006-10-10 20:06:22"
$Date2 = "October 11, 2006 8:06:23 PM"

fnDateDiff($Date1, $Date2, "s") ?
@SERROR ?

? "Press any key to continue." Get $
;

Function fnDateDiff($sDate,$DateOrInterval,Optional $Interval,$Fix)
Dim $objDateTime
$objDateTime = CreateObject("WbemScripting.SWbemDateTime")
If @ERROR Exit 10 EndIf
$objDateTime.SetVarDate($sDate)
If @ERROR Exit 1901 EndIf
$sDate=$objDateTime.GetFileTime

If InStr($DateOrInterval,"/") or InStr($DateOrInterval,":")
$objDateTime.SetVarDate($DateOrInterval)
If @ERROR Exit 1901 EndIf
$DateOrInterval=$objDateTime.GetFileTime
$fnDateDiff=(CDbl($DateOrInterval)-CDbl($sDate))/10000000
Select
Case $Interval="s"
Case $Interval="m" $fnDateDiff=$fnDateDiff/60
Case $Interval="h" $fnDateDiff=$fnDateDiff/3600
Case $Interval="d" $fnDateDiff=$fnDateDiff/86400
Case $Interval="w" $fnDateDiff=$fnDateDiff/604800
Case 1 Exit 87
EndSelect
If $Fix $fnDateDiff=Fix($fnDateDiff) EndIf
Else
If VarType($DateOrInterval)>5 Exit 87 EndIf
Select
Case $Interval="s" If VarType($DateOrInterval)>3 Exit 87 EndIf
Case $Interval="m" $DateOrInterval=$DateOrInterval*60
Case $Interval="h" $DateOrInterval=$DateOrInterval*3600
Case $Interval="d" $DateOrInterval=$DateOrInterval*86400
Case $Interval="w" $DateOrInterval=$DateOrInterval*604800
Case 1 Exit 87
EndSelect
$objDateTime.SetFileTime(''+(CDbl($sDate)/10000000+CDbl($DateOrInterval))+"0000000")
If @ERROR Exit @ERROR EndIf
$fnDateDiff=$objDateTime.GetVarDate
EndIf
EndFunction


Top
#147192 - 2006-10-12 08:58 PM Re: RFC: fnDateDiff() - Perform date calculations
masken Offline
MM club member
*****

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
Exactly as you put it:
Code:
The specified time is invalid.



With:
$Date1 = "2006-10-10 20:06:22"
$Date2 = "2006-10-10 20:06:23"

fnDateDiff($Date1, $Date2, "d") ?
Code:
1,15740740740741E-005



...at least a warning in the header comments?

Top
#147193 - 2006-10-12 10:22 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
"October 11, 2006 8:06:23 PM" may not be a valid date format for your language settings, but you should get the point. It is more flexable.

Yes, however you try the math (and I tried it with all three versions of fnDateDiff), it will "overflow" if the interval is too small for "days" and "weeks".

Top
#147194 - 2006-10-13 10:05 AM Re: RFC: fnDateDiff() - Perform date calculations
masken Offline
MM club member
*****

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
yeah

My only thought was if this otherwise perfect script could have this under control, and report 0 if the interval is too small etc


Edited by masken (2006-10-13 10:05 AM)

Top
#147195 - 2006-10-13 03:00 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Even though the return value is not readable, it can still be evaluated.

Code:

$Date1 = "2006/10/01 20:00:20"
$Date2 = "2006/10/01 20:00:21"

$rc = fnDateDiff($Date1, $Date2, "d")

"9 seconds is the minimum interval that is 'readable' when calculating an" ?
"interval with 'd'." ?
"Less than 9 seconds? " Iif($rc > 0.000104166567325592,"False","True") ?
$rc ?
@ERROR ' : ' @SERROR ?

? "Press any key to continue." Get $


I'm open to suggestions, but changing the output to return '0' just to make it readable could return undesired results.

Top
#147196 - 2006-10-13 03:07 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Another example:

Code:

$Date1 = "2006/10/01 20:00:20"
$Date2 = "2006/10/01 20:00:21"
$Date2 = "2006/10/01 20:00:22"

"Is $$Date2 greater than $$Date3? "
Iif(fnDateDiff($Date1,$Date2,"d") > fnDateDiff($Date1,$Date3,"d"),"True","False") ??

"Is $$Date2 less than $$Date3? "
Iif(fnDateDiff($Date1,$Date2,"d") < fnDateDiff($Date1,$Date3,"d"),"True","False") ??


Top
#147197 - 2006-10-18 05:11 PM Re: RFC: fnDateDiff() - Perform date calculations
masken Offline
MM club member
*****

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
IMHO, if it's set to report days, and the result is an exponential answer, perhaps it could be converted to a non-eponential one?

Or even better, have an optional parameter fed to the function where you can set this? Ie, $ReturnRoundedValues 1/0, or ReturnNonDecimalValues ?


Edited by masken (2006-10-18 05:12 PM)

Top
#147198 - 2006-10-18 10:04 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
Every attempt to round the number I've made fails. If you can come up with one that works I'll consider putting it in.
Top
#147199 - 2006-10-18 10:06 PM Re: RFC: fnDateDiff() - Perform date calculations
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
Paging JOOEL - I hear a challenge
Top
Page 2 of 2 <12


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

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

Generated in 0.069 seconds in which 0.024 seconds were spent on a total of 12 queries. Zlib compression enabled.

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