IsDate() and SQL Server

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( AND Year( gte 1753 />
        <cfcase value="smalldatetime">
            <cfreturn IsDate( AND DateCompare(, '1/1/1900') gte 0 AND DateCompare(, '6/2/2079') lte 0 />

Nothing that someone else couldn't write, but useful. to save headaches.


Related Blog Entries


Andy's Gravatar Bit unnecessary

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

Crypto's Gravatar I think you missed what's going on Andy. What jon's explaining is that SQL Server has a lower boundary on acceptable data values. Let's take a look at the documentation for the function createODBCDateTime()

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">

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!
Jon Hartmann, July 2011

I'm Jon Hartmann and I'm a Javascript fanatic, UX/UI evangelist and former ColdFusion master. I blog about mysterious error messages, user interface design questions, and all things baffling and irksome about programming for the web.

Learn more about me.

Post a job. Find one.

Interested in becoming a sponsor? Contact me.