isDate in VBScript of DTS vs. isDate in MS SQL Query

Hi:

  I am trying to import some dates from a text to a table on MS SQL 2000 using the  ActiveXScript. I am noticing something funny when I use the isDate in VBSCript. It is validating some dates listed below which arent dates (as MS SQL Query will point out). When I use th CDATE next ,as expected, I will get a character cast conversion error.
List of dates isDate in VbSCript of ActiveXScript is validating:
01/01/0101
12/12/1212
There will be so many other examples as I cant control the data set received. Is there a way to correct this issue i.e have isDate work like it does on MS SQL Query Analyser so that CDATE doesnt complain of character cast conversions?

Thanks
LuckyLucksAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>It is validating some dates listed below which arent dates <<
Actually they are valid dates at least as far as VBScript is concerned.  See here fromt the VBScript help for IsDate:

>>IsDate returns True if the expression is a date or is recognizable as a valid date; otherwise, it returns False. In Microsoft Windows, the range of valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the ranges vary among operating systems.<<

Your error is to assume that VBScript IsDate function is the same as the T-SQL ISDATE() that is plainly not the case.

The solution is to use a function something like this (Untested):

Function IsValidTSQLDateTime (ByVal sDat)
Const FROM_DATE = #1/1/1753#, _
    TO_DATE = #12/31/9999#

Dim Dat

If IsDate(sDat) Then
   Dat = CDate(sDat)
   If Dat >=  FROM_DATE and Dat <= TO_DATE Then
       IsValidTSQLDateTime = Dat
   Else
       IsValidTSQLDateTime = Null
Else
    IsValidTSQLDateTime = Null
End If

End Function

I will leave it as an exercise for the similar IsValidTSQLSmallDateTime() function.
0
 
RickBeebeCommented:
Does it work if you concantenate pound signs?

IsDate(#01/01/0101#)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.