Friday, May 15, 2009

Brain-sucking Date-Mashing Code

So, you may have already looked at the code I posted that shows how to invoke a function or object from one scripting engine to another.  For example, using the ScriptControl object API to call upon the Cosine function in JScript from KiXtart, or call the @PRIV macro in KiXtart from VBScript.  Nothing Earth-shattering in that.  But this was an interesting spin.

KiXtart doesn't have a built-in counterpart to the VBScript DateDiff() function (among other related functions).  There are UDF code chunks out there which are very good and very reliable, but there's another way (did you guess yet?): You can invoke the DateDiff() function in VBscript from KiXtart.  The trick is in how you prepare the expression before asking the ScriptControl object to "evaluate" it and return a result.  This applies to DateAdd() and pretty much all of the other goodies WSH+VBScript/JScript has to offer.

So which of the following three (without cheating) do you think is the one that actually works?

Example 1

Function DaysOld($date)
Dim $sc, $result, $cmd
$cmd = "DateDiff('d', "+Chr(34)+$date+Chr(34)+", Now)"
$sc = CreateObject("ScriptControl")
$sc.Language = "vbscript"
$result = $sc.Eval($cmd)
$sc = 0
$DaysOld = $result
EndFunction




Example 2





Function DaysOld($date)
Dim $sc, $result, $cmd
$cmd = "DateDiff("+Chr(34)+"d"+Chr(34)+", "+Chr(34)+$date+Chr(34)+", Now)"
$sc = CreateObject("ScriptControl")
$sc.Language = "vbscript"
$result = $sc.Eval($cmd)
$sc = 0
$DaysOld = $result
EndFunction




Example 3





Function DaysOld($date)
Dim $sc, $result, $cmd
$cmd = "DateDiff(d, "+Chr(34)+$date+Chr(34)+", Now())"
$sc = CreateObject("ScriptControl")
$sc.Language = "vbscript"
$result = $sc.Eval($cmd)
$sc = 0
$DaysOld = $result
EndFunction




 



The answer?  Is further down below...



 



 



The correct answer is example 2.  It's weird that unless you wrap the date values in double-quotes, you get nothing back.  The NOW object doesn't require that however.  Each COM object you work with via the ScriptControl conduit behaves a little differently based on how the object's own API is written and how values are passed through the pipeline.  But hopefully you can see there's some value to this.



So, a more esoteric version of DateDiff() done with this approach might be something like this...





Function DateDiff($mode, $date1, $date2)
Dim $sc, $result, $cmd
$cmd = "DateDiff("+Chr(34)+$mode+Chr(34)+", "+Chr(34)+$date+Chr(34)+", "+Chr(34)+$date+Chr(34)+")"
$sc = CreateObject("ScriptControl")
$sc.Language = "vbscript"
$result = $sc.Eval($cmd)
$sc = 0
$DateDiff = $result
EndFunction

No comments: