CFLib.org – Common Function Library Project

IsSQLServerDate(date[, type])

Last updated June 19, 2013

author

Jon Hartmann

Version: 1 | Requires: CF5 | Library: DateLib

Description:
Validates a string as valid datetime or smalldatetime string for SQL Server. Checks if the input is date and also if it is within the date range acceptable to to datetime or smalldatetime

Return Values:
returns a boolean

Example:

Is 12/25/1752 valid SQL Server datetime? <cfdump var="#IsSQLServerDate('12/25/1752')#"><br />
Is 12/25/2002 valid SQL Server datetime? <cfdump var="#IsSQLServerDate('12/25/2002')#"><br />
Is 12/25/9999 valid SQL Server datetime? <cfdump var="#IsSQLServerDate('12/25/9999')#"><br />
Is 12/25/1899 valid SQL Server smalldatetime? <cfdump var="#IsSQLServerDate('12/25/1899', 'smalldatetime')#"><br />
Is 12/25/2002 valid SQL Server smalldatetime? <cfdump var="#IsSQLServerDate('12/25/2002', 'smalldatetime')#"><br />
Is 12/25/2099 valid SQL Server smalldatetime? <cfdump var="#IsSQLServerDate('12/25/2099', 'smalldatetime')#">

Parameters:

Name Description Required
date Date to check Yes
type Type - smalldatetime or datetime No

Full UDF Source:

<!---
 Validates a string as valid datetime or smalldatetime string for SQL Server.
 
 @param date 	 Date to check (Required)
 @param type 	 Type - smalldatetime or datetime (Optional)
 @return returns a boolean 
 @author Jon Hartmann (jon.hartmann@gmail.com) 
 @version 1, June 19, 2013 
--->
<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>
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson