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.
Comments
Anything that passes isdate() will be converted by createodbcdatetime(), so just use that to convert your string before using inside your update or insert statement
AB
Syntax: CreateODBCDateTime(date)
Where date is a Date-time object in the range 100 AD-9999 AD.
<cfset myDate = CreateODBCDateTime('9/11/1714') /> <!--- national day of catalonia...what? i like history... --->
<cfif isdate(myDate)>
<cfquery name="sql" datasource="cookiecoders">
INSERT INTO dateData(myDate) VALUES(<cfqueryparam value="#myDate#" cfsqltype="cf_sql_date">
</cfquery>
</cfif>
executing this (with the proper datasource setup etc...) will result in this error
[Macromedia][SQLServer JDBC Driver]The value, 1714-09-11 00:00:00.0, is outside the range allowed by the SQL Server.
however, using jon's code as listed will catch this. nice work jon!