IsDate() and SQL Server
- January 10, 2008 3:54 PM
- ColdFusion, Utility Function, Microsoft Tools
- Comments (2)
I don't know about you, but my company uses the IsDate() function for a lot of simple validation of date inputs, and it works fairly well for the most part. The only problem I have with it is that when paired with SQL Server (2000 or 2005), it still can't capture all invalid dates.
The date 'January 5, 753' is a valid date, but attempting to insert it into a SQL Server datetime object throws an error because valid dates for SQL Server are between January 1, 1753 and December 31, 9999. Dates above the year 9999 are caught by IsDate(), but the lower bound is not.
<cffunction name="IsSQLServerDate" returntype="boolean" output="false">
<cfargument name="date" type="date" required="true"/>
<cfargument name="type" type="string" required="false" default="datetime"/>
<cfswitch expression="#arguments.type#">
<cfcase value="datetime">
<cfreturn IsDate(arguments.date) AND Year(arguments.date) gte 1753 />
</cfcase>
<cfcase value="smalldatetime">
<cfreturn IsDate(arguments.date) AND DateCompare(arguments.date, '1/1/1900') gte 0 AND DateCompare(arguments.date, '6/2/2079') lte 0 />
</cfcase>
</cfswitch>
</cffunction>
Nothing that someone else couldn't write, but useful. to save headaches.