Chris S.
(MM club member)
2005-09-06 03:24 PM
RFC: fnDateDiff() - Perform date calculations

This function will calculate the interval between two dates -OR- add an interval to a specified date. Supports the following intervals; seconds, minutes, hours, days, and weeks.

I'm asking for comments or suggestions for improvement.

Thanks!

"One second after 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,"2006/04/20","d") ??
"Seconds between 1/1/1601 and 1/1/1970: " + fnDateDiff("01/01/1601","01/01/1970") ??
"Hours between NOW and 9/18/2005 13:00: " + fnDateDiff(@DATE+' '+@TIME,"09/18/2005 13:00:00","h") ??
"Seconds between midnight and 11:59:59 pm: " + fnDateDiff("00:00:00","23:59:59","s",0) ??
"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) ??
@SERROR ?
 
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

Chris S.
(MM club member)
2005-09-06 03:26 PM
Re: RFC: fnDateDiff() - Perform date calculations

BTW, this requires Windows XP.

LonkeroAdministrator
(KiX Master Guru)
2005-09-06 03:43 PM
Re: RFC: fnDateDiff() - Perform date calculations

right.
suggestion, make it not require candyOS.

Chris S.
(MM club member)
2005-09-07 05:21 PM
Re: RFC: fnDateDiff() - Perform date calculations

Lonk, you're a prick, but here is a version that doesn't require anything but KiX.

"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,"2006/04/20","d") ?
"Seconds between 1/1/1601 and 1/1/1970: " + fnDateDiff("1601/01/01","1970/01/01","s") ?
"Hours between NOW and 9/18/2005 13:00: " + fnDateDiff(@DATE+' '+@TIME,"2005/09/18 13:00:00","h") ?
"Yesterday's date: " + fnDateDiff(@DATE,-1,"d") ?
"Seconds between midnight and 11:59:59 pm: " + fnDateDiff("00:00:00","23:59:59","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) ?
@SERROR ?
 
Get $
 
Function fnDateDiff($DateTime,$DateOrInterval,Optional $Interval,$Fix)
   Dim $jDate[2],$jTime[2],$y,$m,$d,$ss,$mm,$hh,$1899,$i
   $DateTime=Split($DateTime," ")
   If UBound($DateTime)>1 Exit 1901 EndIf
   If UBound($DateTime)=0
      Select
      Case InStr($DateTime[0],"/") $jDate=Split($DateTime[0],"/")
      Case InStr($DateTime[0],":") $jDate=Split("1899/12/30","/") $jTime=Split($DateTime[0],":") $1899=1
      Case 1 Exit 1901
      EndSelect
   Else
      If InStr($DateTime[0],"/") and InStr($DateTime[1],":")
         $jDate=Split($DateTime[0],"/") 
         $jTime=Split($DateTime[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
   If $m < 3 $m=$m+12 $y=$y-1 EndIf
   $jDate = CDbl($d+(153*$m-457)/5+365*$y+$y/4-$y/100+$y/400-306)
   $jTime = CDbl((Val($jTime[0])*3600)+(Val($jTime[1])*60)+Val($jTime[2]))/86400
   If $jTime=>1 Exit 1901 EndIf
   $DateTime=CDbl($jDate+$jTime)*86400
   
   If InStr($DateOrInterval,"/") or InStr($DateOrInterval,":")
      ReDim $jDate[2],$jTime[2],$y,$m,$d
      $DateOrInterval=Split($DateOrInterval," ")
      If UBound($DateOrInterval)>1 Exit 1901 EndIf
      If UBound($DateOrInterval)=0
         Select
         Case InStr($DateOrInterval[0],"/") $jDate=Split($DateOrInterval[0],"/")
         Case InStr($DateOrInterval[0],":")
            $jTime=Split($DateOrInterval[0],":")
            $jDate=Split("1899/12/30","/")
            If $1899=0 Exit 1804 EndIf
         Case 1 Exit 1901
         EndSelect
      Else
         If InStr($DateOrInterval[0],"/") and InStr($DateOrInterval[1],":")
            $jDate=Split($DateOrInterval[0],"/") 
            $jTime=Split($DateOrInterval[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
      If $m < 3 $m=$m+12 $y=$y-1 EndIf
      $jDate = CDbl($d+(153*$m-457)/5+365*$y+$y/4-$y/100+$y/400-306)
      $jTime = CDbl((Val($jTime[0])*3600)+(Val($jTime[1])*60)+Val($jTime[2]))/86400
      If $jTime>1 Exit 1901 EndIf
      $DateOrInterval=CDbl($jDate+$jTime)*86400
      $fnDateDiff=$DateOrInterval-$DateTime
      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=($DateTime+$DateOrInterval)/86400
      $jTime=$fnDateDiff-Fix($fnDateDiff)
      $jDate=Fix($fnDateDiff)
      
      $y = (100*(((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4))+
         (100*($jDate+306)-25))/36525 
      $m = (5*(((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4)+
         ($jDate+306)-365*$y-$y/4)+456)/153 
      $d = (((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4)+
         ($jDate+306)-365*$y-$y/4)-(153*$m-457)/5 
      If $m>12 $y=$y+1 $m=$m-12 EndIf
      If Len($y)<4 $ = Execute("For $$i=1 to 4-len($$y) $$y = '0' + $$y Next") 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($1899,'',$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
EndFunction

LonkeroAdministrator
(KiX Master Guru)
2005-09-07 09:40 PM
Re: RFC: fnDateDiff() - Perform date calculations

that's better.

Chris S.
(MM club member)
2005-09-07 10:50 PM
Re: RFC: fnDateDiff() - Perform date calculations

I had an idea to make the function recursive to eliminate some code redundancy...

"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,"2006/04/20","d") ?
"Seconds between 1/1/1601 and 1/1/1970: " + fnDateDiff("1601/01/01","1970/01/01","s") ?
"Hours between NOW and 9/18/2005 13:00: " + fnDateDiff(@DATE+' '+@TIME,"2005/09/18 13:00:00","h") ?
"Hours between 9/18/2005 13:00 and NOW: " + fnDateDiff("2005/09/18 13: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) ?
@SERROR ?
 
Get $
 
Function fnDateDiff($DateTime,Optional $DateOrInterval,$Interval,$Fix)
   Dim $Date1,$Date2,$jDate[2],$jTime[2],$y,$m,$d,$ss,$mm,$hh,$i
   $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
   If $m < 3 $m=$m+12 $y=$y-1 EndIf
   $jDate = CDbl($d+(153*$m-457)/5+365*$y+$y/4-$y/100+$y/400-306)
   $jTime = CDbl((Val($jTime[0])*3600)+(Val($jTime[1])*60)+Val($jTime[2]))/86400
   If $jTime=>1 Exit 1901 EndIf
   $Date1=CDbl($jDate+$jTime)*86400
   
   If VarType($DateOrInterval)
      If InStr($DateOrInterval,"/") or InStr($DateOrInterval,":")
         $Date2=fnDateDiff($DateOrInterval)
         $fnDateDiff=$Date2-$Date1
         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)
         
         $y = (100*(((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4))+
            (100*($jDate+306)-25))/36525 
         $m = (5*(((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4)+
            ($jDate+306)-365*$y-$y/4)+456)/153 
         $d = (((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4)+
            ($jDate+306)-365*$y-$y/4)-(153*$m-457)/5 
         If $m>12 $y=$y+1 $m=$m-12 EndIf
         If Len($y)<4 $ = Execute("For $$i=1 to 4-len($$y) $$y = '0' + $$y Next") 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

NTDOCAdministrator
(KiX Master)
2005-09-08 12:29 AM
Re: RFC: fnDateDiff() - Perform date calculations

5,431 - 4,631 = 800 characters shaved off. Okay, whose next for this round of Golf.

Chris S.
(MM club member)
2005-09-08 03:34 AM
Re: RFC: fnDateDiff() - Perform date calculations

If I were going to golf it, I'd start with the CandyOS code.

LonkeroAdministrator
(KiX Master Guru)
2005-09-08 11:56 AM
Re: RFC: fnDateDiff() - Perform date calculations

I would start from long var names.

iffy
(Starting to like KiXtart)
2005-09-18 05:47 PM
Re: RFC: fnDateDiff() - Perform date calculations

Very useful, happened to need something like this just this week and by now using it in 3 different scripts. I vote for inclusion in the UDF lib

swat_76
(Lurker)
2005-11-04 09:50 AM
Re: RFC: fnDateDiff() - Perform date calculations

great function !!!
date calculation and handling is very poor until now !!!
i hope this and some other functions will be included in the next version !!!
habe d'ehre

NTDOCAdministrator
(KiX Master)
2006-05-18 10:04 PM
Re: RFC: fnDateDiff() - Perform date calculations

Chris,

Why not post this as a UDF ?

Seems some people now use it and you referenced it in another post.

Much easier to find as a UDF than as a Script Forum post.

Chris S.
(MM club member)
2006-05-18 10:31 PM
Re: RFC: fnDateDiff() - Perform date calculations

Since posting this I've been pretty busy with a major SMS deployment and haven't had time to document the header, but I'll put this on my to-do list.

masken
(MM club member)
2006-10-03 03:37 PM
Re: RFC: fnDateDiff() - Perform date calculations

this kicks *ss. I had a really old script that I wrote in... 2002 I think for this. Hope you find the time to "functionalize" this one Chris as it's a really good one

LonkeroAdministrator
(KiX Master Guru)
2006-10-03 04:01 PM
Re: RFC: fnDateDiff() - Perform date calculations

functionalize what?

you mean add the proper standard UDF headers?

LonkeroAdministrator
(KiX Master Guru)
2006-10-03 04:04 PM
Re: RFC: fnDateDiff() - Perform date calculations

oh, sorry.
just noticed, this is in wrong forum

masken
(MM club member)
2006-10-03 05:19 PM
Re: RFC: fnDateDiff() - Perform date calculations

yeah, proper headers etc so everyone knows how to use it

Chris S.
(MM club member)
2006-10-03 05:54 PM
Re: RFC: fnDateDiff() - Perform date calculations

I'm working on it. I'll try to get it out within a week or so.

In the mean time... does anyone have time to do any further testing on the last posted code? Are there any feature requests or further comments?

Chris S.
(MM club member)
2006-10-03 06:55 PM
Re: RFC: fnDateDiff() - Perform date calculations

Ok. Here is the latest with headers. I'll post to the UDF forum if there are no objections in one week's time.

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: 
; fnLDAPQuery(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 Peter Baum (http://www.capecod.net/~pbaum/date/date0.htm)
; 
;Returns: 
; 
; A Datetime string or the difference (positive or negative) between two given datetimes.
; 
; 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,"2006/04/20","d") ?
; "Seconds between 1/1/1601 and 1/1/1970: " + fnDateDiff("1601/01/01","1970/01/01","s") ?
; "Hours between NOW and 9/18/2005 13:00: " + fnDateDiff(@DATE+' '+@TIME,"2005/09/18 13:00:00","h") ?
; "Hours between 9/18/2005 13:00 and NOW: " + fnDateDiff("2005/09/18 13: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) ?
; Get $
; 
 
Function fnDateDiff($DateTime,Optional $DateOrInterval,$Interval,$Fix)
   Dim $DATE1,$Date2,$jDate[2],$jTime[2],$y,$m,$d,$ss,$mm,$hh,$i
   $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
   If $m < 3 $m=$m+12 $y=$y-1 EndIf
   $jDate = CDbl($d+(153*$m-457)/5+365*$y+$y/4-$y/100+$y/400-306)
   $jTime = CDbl((Val($jTime[0])*3600)+(Val($jTime[1])*60)+Val($jTime[2]))/86400
   If $jTime=>1 Exit 1901 EndIf
   $DATE1=CDbl($jDate+$jTime)*86400
   
   If VarType($DateOrInterval)
      If InStr($DateOrInterval,"/") or InStr($DateOrInterval,":")
         $Date2=fnDateDiff($DateOrInterval)
         $fnDateDiff=$Date2-$DATE1
         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)
         
         $y = (100*(((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4))+
            (100*($jDate+306)-25))/36525 
         $m = (5*(((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4)+
            ($jDate+306)-365*$y-$y/4)+456)/153 
         $d = (((100*($jDate+306)-25)/3652425)-(((100*($jDate+306)-25)/3652425)/4)+
            ($jDate+306)-365*$y-$y/4)-(153*$m-457)/5 
         If $m>12 $y=$y+1 $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

ChristopheM
(Hey THIS is FUN)
2006-10-04 10:03 PM
Re: RFC: fnDateDiff() - Perform date calculations

before posting to UDF library, just modify the header :
;Syntax:
; fnLDAPQuery(DATE1, [DATE2|INTEGER], [INTERVAL], [FIX])

this is not really fnLDAPQuery function but fnDateDiff

!!!

Chris S.
(MM club member)
2006-10-04 10:07 PM
Re: RFC: fnDateDiff() - Perform date calculations

Now you know where I grabbed the header!

NTDOCAdministrator
(KiX Master)
2006-10-04 10:08 PM
Re: RFC: fnDateDiff() - Perform date calculations

Nice catch.

Chris S.
(MM club member)
2006-10-06 04:49 PM
Re: RFC: fnDateDiff() - Perform date calculations

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

masken
(MM club member)
2006-10-10 09:22 PM
Re: RFC: fnDateDiff() - Perform date calculations

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")

NTDOCAdministrator
(KiX Master)
2006-10-10 09:53 PM
Re: RFC: fnDateDiff() - Perform date calculations

What did it report? I'd think it should have reported 0

Chris S.
(MM club member)
2006-10-10 11:12 PM
Re: RFC: fnDateDiff() - Perform date calculations

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.

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

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.

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

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.

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

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

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

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?

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

"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".

masken
(MM club member)
2006-10-13 10:05 AM
Re: RFC: fnDateDiff() - Perform date calculations

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

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

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.

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

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") ??

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

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 ?

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

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.

NTDOCAdministrator
(KiX Master)
2006-10-18 10:06 PM
Re: RFC: fnDateDiff() - Perform date calculations

Paging JOOEL - I hear a challenge