Page 1 of 2 12>
Topic Options
#147163 - 2005-09-06 03:24 PM RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
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

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

Registered: 2002-03-18
Posts: 2368
Loc: Earth
BTW, this requires Windows XP.
Top
#147165 - 2005-09-06 03:43 PM Re: RFC: fnDateDiff() - Perform date calculations
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22343
Loc: OK
right.
suggestion, make it not require candyOS.
_________________________
!

download KiXnet

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

Registered: 2002-03-18
Posts: 2368
Loc: Earth
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

Top
#147167 - 2005-09-07 09:40 PM Re: RFC: fnDateDiff() - Perform date calculations
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22343
Loc: OK
that's better.
_________________________
!

download KiXnet

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

Registered: 2002-03-18
Posts: 2368
Loc: Earth
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

Top
#147169 - 2005-09-08 12:29 AM Re: RFC: fnDateDiff() - Perform date calculations
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11606
Loc: CA
5,431 - 4,631 = 800 characters shaved off. Okay, whose next for this round of Golf.
Top
#147170 - 2005-09-08 03:34 AM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
If I were going to golf it, I'd start with the CandyOS code.
Top
#147171 - 2005-09-08 11:56 AM Re: RFC: fnDateDiff() - Perform date calculations
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22343
Loc: OK
I would start from long var names.
_________________________
!

download KiXnet

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

Registered: 2005-05-29
Posts: 149
Loc: The Netherlands
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
Top
#147173 - 2005-11-04 09:50 AM Re: RFC: fnDateDiff() - Perform date calculations
swat_76 Offline
Lurker

Registered: 2005-11-04
Posts: 1
Loc: Asstria
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
_________________________
KIXtart pimps my Scripts !!!

Top
#147174 - 2006-05-18 10:04 PM Re: RFC: fnDateDiff() - Perform date calculations
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11606
Loc: CA
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.

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

Registered: 2002-03-18
Posts: 2368
Loc: Earth
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.
Top
#147176 - 2006-10-03 03:37 PM Re: RFC: fnDateDiff() - Perform date calculations
masken Offline
MM club member
*****

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
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
Top
#147177 - 2006-10-03 04:01 PM Re: RFC: fnDateDiff() - Perform date calculations
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22343
Loc: OK
functionalize what?

you mean add the proper standard UDF headers?

Top
#147178 - 2006-10-03 04:04 PM Re: RFC: fnDateDiff() - Perform date calculations
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22343
Loc: OK
oh, sorry.
just noticed, this is in wrong forum

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

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
yeah, proper headers etc so everyone knows how to use it
Top
#147180 - 2006-10-03 05:54 PM Re: RFC: fnDateDiff() - Perform date calculations
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
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?

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

Registered: 2002-03-18
Posts: 2368
Loc: Earth
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



Edited by Chris S. (2006-10-03 07:00 PM)

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

Registered: 2002-05-13
Posts: 307
Loc: STRASBOURG, France
before posting to UDF library, just modify the header :
;Syntax:
; fnLDAPQuery(DATE1, [DATE2|INTEGER], [INTERVAL], [FIX])

this is not really fnLDAPQuery function but fnDateDiff

!!!
_________________________
Christophe

Top
Page 1 of 2 12>


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

Who's Online
0 registered and 140 anonymous users online.
Newest Members
vertage, dimi575, Drecksnacken, Nlill, webtools321
17735 Registered Users

Generated in 0.045 seconds in which 0.012 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