Datetime Field Overflow Error

[joepeacock]

My client sent me an error message they received:
ODBC Error Code = 22008 (Datetime field overflow)

This is happening during a CFINSERT tag. (I know a CFQUERY would ordinarily be preferred. There are a number of reasons I used a CFINSERT in this case. If you are just going to tell me to re-write it with a CFQUERY, don't bother.)

I know this is often caused by trying to enter a date that is out of range, or in European format. The problem is, I can't replicate the error. The date that is input into the field is checked for validity using ISDATE() function a couple of templates before the insert and any invalid date is rejected.

I have tried entering dates in DD/MM/YY, DD/MM/YYYY, DD-MM-YYYY, and all kinds of other non-standard formats. Every time it is either rejected by the ISDATE() function, or is permitted by the CFINSERT. But somehow the client managed to pass a date by the ISDATE() that caused an error with the CFINSERT.

Any ideas what the client entered that would do that? I need to set up handling for it, I guess, but I don;t know where to start.

Using MS SQL Server and CF 5.0 on a Win2K machine

Thanks in advance -
- Joe

 

[SidianConsulting]

Use CFQUERY with SQL!

But seriously...

Since you are using one of ColdFusion's &quot;Crutch Functions&quot;, as I like to call both CFINSERT and CFUPDATE, the workings of this operation are all abstracted, which is bad for you because troubleshooting is a lot harder. What you COULD do, is use <cftry> and <cfcatch> to trap the error and then either:

1) Write it to a file
2) Email it to yourself
3) Write it to a database field somewhere

Also, what is the datatype for the field that holds the date/time value? Is it datetime or smalldatetime? A valid date to CF is not always a valid date to SQL Server; check the SQL Server Books Online (in the SQL Server program group folder in the start menu) and make sure you are using the correct datatype (datetime or smalldatetime) for the date ranges your program requires. The error you mention can happen when you enter a date that SQL Server cannot handle, yet ColdFusion sees as a valid date.

In the spirit of helpfulness (of course), I'd like to challenge you to tell us all what reason(s) you have for using CFINSERT instead of using a straight CFQUERY.

-Tek

 

[joepeacock]

Tek,

My reasons are, of course, selfish. I have about 8 forms that feed data to this query. each of those forms is generated dynamically based on the results of previous forms, so there may be 20 to 25 different sets of fields being fed to the query. Rather than building a CFQUERY with three pages of CFIF/CFELSEIF/CFELSE inside of it, and billing my client for all that time, I used the crutch. The project was intended to be done as quickly and cheaply as possible. (Now that I think about it, all my jobs are like that. I want a client who's wiling to spend more to have it done right and well.)