Stop throwing SQL Server ColdFusion date error

The follow error is thrown every once in a while - exception
The date is selected from date picker in a form  from previous page and passed
to the form action page  . I tried to stop this by putting in the code below, but obviously does
not work.

SQLServer JDBC Driver][SQLServer]Arithmetic overflow error converting expression to data type smalldatetime
<!--- Previous Page date selection --->
									<div class="input_row">
										<label class="input_label" for="from">From</label>
										<div class="inputWrapper">
											<input type="text" class="datepicker" id="from" name="availfromdate">
										</div>
									</div>
									<cfif BrowserCountry is "United States">								
										<!--- US dateformat ---> <input type="hidden" name="DateFormat" value="mm-dd-yy"> 
										<script>
										// File jQuery UI datepiucker using US date format
										$(function($) {
											$(document).ready(function() {
												$('.datepicker').datepicker({ dateFormat: 'mm-dd-yy' });
											});
										});
										</script>                         
                                        <cfelse>
                                        
                                        <!--- AU dateformat ---> <input type="hidden" name="DateFormat" value="dd-mm-yy"> 
										<script>
										// File jQuery UI datepiucker using AU date format
										$(function($) {
											$(document).ready(function() {
												$('.datepicker').datepicker({ dateFormat: 'dd-mm-yy' });
											});
										}); 
										</script>
                                        </cfif>
			

<!--- fix date exception error --->      
         <cfif IsDefined("form.availfromdate")
	    and len(trim(form.availfromdate))
		and   isValid("date", form.availfromdate)
		and   IsDate(FORM.availfromdate)>
	  		<cfset HunkDory = "true">
 		<cfelse>
      		<cfset form.availfromdate = DateAdd('d', 30, Now())>
 	</cfif>
.....
Error thrown in update 

<CFQUERY dataSource="fooSource"> 
UPDATE Members
Set

Country          = '#form.country#',
AvailFromDate    =  #form.AvailFromDate#,

WHERE UserName =   <cfqueryparam value='#form.UserName#' maxlength="20" cfsqltype="cf_sql_varchar"> 
</CFQUERY>

Open in new window

Ian WhiteOwner and FounderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
What is your date column defined as in your database table?

The follow error is thrown every once in a while
Which date type does this error occur on - U.S. or AU?
LajuanTaylorCommented:
I ran a quick test and it appears that SQL does not like the format of the date you inserting.

CREATE TABLE [dbo].[tblDateTest](
	[availfromdate1] [smalldatetime] NULL,
	[availfromdate2] [datetime] NULL,
	[availfromdate3] [date] NULL
) ON [PRIMARY]


--mm-dd-yy
INSERT INTO [DDS].[dbo].[tblDateTest]
           ([availfromdate1]
           ,[availfromdate2]
           ,[availfromdate3])
     VALUES
           ('10-13-15'
           ,'10-13-15'
           ,'10-13-15')
GO

--dd-mm-yy
INSERT INTO [DDS].[dbo].[tblDateTest]
           ([availfromdate1]
           ,[availfromdate2]
           ,[availfromdate3])
     VALUES
           ('13-10-15'
           ,'13-10-15'
           ,'13-10-15')
GO

Open in new window

2015-05-08-2024-Date-Format-Issue.png
Mark ElySenior Coldfusion DeveloperCommented:
You should always use CFQUERYPARAM  to protect yourself from SQL injection.  You needed to properly format the date field..  try the following

<!--- Previous Page date selection --->
<div class="input_row">
	<label class="input_label" for="from">From</label>
	<div class="inputWrapper">
		<input type="text" class="datepicker" id="from" name="availfromdate">
	</div>
</div>
<cfif BrowserCountry is "United States">								
	<!--- US dateformat ---> <input type="hidden" name="DateFormat" value="mm-dd-yy"> 
	<script>
	// File jQuery UI datepiucker using US date format
	$(function($) {
		$(document).ready(function() {
			$('.datepicker').datepicker({ dateFormat: 'mm-dd-yy' });
		});
	});
	</script>                         
    <cfelse>
    
    <!--- AU dateformat ---> <input type="hidden" name="DateFormat" value="dd-mm-yy"> 
	<script>
	// File jQuery UI datepiucker using AU date format
	$(function($) {
		$(document).ready(function() {
			$('.datepicker').datepicker({ dateFormat: 'dd-mm-yy' });
		});
	}); 
	</script>
    </cfif>
			

<!--- fix date exception error --->      
<CFIF IsDefined ("form.availfromdate") AND IsDate(form.availfromdate)>
	<CFSET form.availfromData = DATEFORMAT(form.availfromData, "yyyy-mm-dd")>
<cfelse>
	<cfset form.availfromdate = DATEFORMAT(DateAdd('d', 30, Now()), "yyyy-mm-dd")>
</CFIF>

<CFQUERY dataSource="fooSource"> 
	UPDATE Members
	Set	Country = <cfqueryparam value='#form.country#' cfsqltype="cf_sql_varchar">,
	    AvailFromDate    = <cfqueryparam value="#form.AvailFromDate#" cfsqltype="date">
WHERE UserName =   <cfqueryparam value='#form.UserName#' maxlength="20" cfsqltype="cf_sql_varchar"> 
</CFQUERY>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ian WhiteOwner and FounderAuthor Commented:
It is smalldatetime in database.  I am unable to determine which one is throwing the error US or AU date format. It only happens say once ever month. So hundreds go through normally . Registrations are from all over the world.
Ian WhiteOwner and FounderAuthor Commented:
Thanks I will give that a test. I understand numeric and smalldateint feilds  are safe from sql injection as you cant insert code into these fields. But I take your point for country
LajuanTaylorCommented:
Mark Ely's code sample will insure that the the date is formatted correctly before being inserted into the database.

Here's a good resource that explains how the datetime data types work in SQL Server, and explains some common pitfalls and general recommendations.
http://www.karaszi.com/sqlserver/info_datetime.asp
_agx_Commented:
I understand numeric and smalldateint feilds  are safe from sql injection as you cant insert code into these fields

Inserting code into a db column value is a different type of injection.  Numeric fields are definitely vulnerable to the standard sql injection techniques.  A malicious user could submit a fake form post with a value like  "NULL;DROP TABLE TableNameA;" and because your query isn't using cfqueryparam it would delete one of your tables... or worse.  

Keep in mind that just because it may be a little harder to attack certain field types, doesn't mean it's impossible.  Nothing is a 100% safe.  As Mark said, always use cfqueryparam.  Not just to guard against sql injection protection, but to guarantee you're sending the values to the db in the proper format.  That's especially important with dates.  

<cfqueryparam value="#form.AvailFromDate#" cfsqltype="date">

Though be certain to use the correct cfsqltype. (I think Mark meant to type CF_SQL_DATE. DATE isn't a valid type, so it's like using CF_SQL_CHAR which isn't good). Typically you use CF_SQL_DATE if you want to insert a date only (no time value) and CF_SQL_TIMESTAMP to insert both a date and time.  I rarely use "smalldatetime" but apparently the SQL Server JDBC docs say to the correct type for smalldatetime is java.sql.Timestamp. In CF lingo that would be:

         <cfqueryparam value="#form.AvailFromDate#" cfsqltype="CF_SQL_TIMESTAMP">

I tried to stop this by putting in the code below, but obviously does
not work.

The question I would ask is what is the actual form field value when that happens? It's difficult to say why it's happening without seeing the problem value.  Dump the form scope in your error handler and log or email it so you can review it later.

As an aside, the problem with relying on CF's date functions is that most only work with US dates and they accept a LOT of stuff they shouldn't. Consequently they return bizarre results on occasion.  For examplethis blog shows how IsDate("1.5") produces YES.  Most folks would expect it to return NO.  For predictable results, you really have to roll your own validation.  Especially if your app is receiving a mix of date strings, like mm/dd and dd/mm.  Otherwise you are at the mercy of CF's crazy date string parser in which case .. well ... expect the unexpected ;-)
Ian WhiteOwner and FounderAuthor Commented:
Thanks I don't really have the expertise, or inclination, to do my own date checking.  Anyway why reinvent the wheel?  I think I will just do a <cftry> and <cfcatch> and any database error do some default logic to add a dat.
_agx_Commented:
It's not reinventing the wheel. The functionality doesn't really exist in CF.  The CF date functions are a nice try, but like I said above, they're too lenient and not always accurate, so problems are inevitable.  Even using try/catch won't always help with functions like IsDate or IsValid.  Just because a function doesn't throw an error doesn't mean you've got the right result.  The date string "1.5" is a perfect example.  You wouldn't consider that a valid date, but IsDate() does.  As a result, you'd end up inserting 1899-12-31, or some other wrong date into your db. Almost certainly not what you want.

Validation isn't difficult. Just decide what format you want to accept and write a small function to validate it.  There was a good thread about this on SO.  To paraphrase, say for example you wanted to accept mm/dd/yyyy format

1. Use a simple regex to verify the input matches that pattern \d{2}\\\d{2}\\\d{4}
2. Treat the string as a List separated by "/" and use list functions to extract the month, day and year.
3. Create a date object with those values (this is a good place for a try catch). If it doesn't error, the input is valid.

It's your choice, but depending on CF functions alone is rolling the dice. The only way to be absolutely sure you've got the right input, per to your application rules, is to DIY.
Ian WhiteOwner and FounderAuthor Commented:
Thanks for the great suggestion.  The error only happens say once a month - and is thrown when the invalid dates is attempted to be inserted in the database. So I will just trap that error.  I prefer to put my time into improving the business and revenue flow - and look and feel of my website using bootstrap frameworks etc . I just want to put time in where it produces the greatest return. So for me it is not worth writing some routine (which I dont have the skills for) to stop 1 /1000 registrations falling over. I think Adobe should just wake up to themselves.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.