Help With Date Trouble!!!

[gasparrelli]

We are using cfstoredproc to insert and update records in our Oracle database. We accept dates through our user interface as mm/dd/yyyy and these are jscript validated. Trouble is that one of our CF servers using the oracle native 8.0 driver is having trouble sending "correct" dates and we aren't sure how to fix this.

For example, a user entering 12/01/2000 (intending mm/dd/yyyy)would get a record in the oracle db for 12-JAN-2000. If the user enters 12/30/2000 however oracle would correctly get 30-DEC-2000. Does anyone know how to fix this or what is causing it?

It seems that CF is sending oracle in its cfprocparam bind parameters (which we are passing as SQL_Date) what the user enters as the days in the months place, unless the day is out of range as a month, in which case it correctly passes mm/dd/yyyy.

This is only happening on one of our CF servers also, another CF server in development running the same app doesn't make this mistake.

THanks for any help!

 

[perichazhi]

please check the Datye format in the Regional Settings of the Server. If it is dd/mm/yyyy, then this might be the reason for wrong dates being entered.

All the best
Perichazh

 

[gasparrelli]

Thanks for the suggestion - that's one of the things I looked into and that doesn't seem to have an effect. I have opened a trouble ticket with Allaire and they are telling me so far to rewrite all my sp's with to_date and change all my cfprocparams from cfsqltype DATE to cfsqltype VARCHAR. This seems decidedly like a work-around and not a solution to me. They cannot tell me why this is happening on one CF server and not the other either, nor how their native driver checks for date format. I'm pressing on this so will post anything further they give me.

By the way the server we are having trouble with is running SP1 for 4.5 Enterprise, that is the only difference from our other CF servers...

 

[KevinFSI]

Just a thought:
Look into the CreateODBCDate() and DateFormat() functions to manipulate the way the data is input into your database.

Kevin

 

[Guest_imported]

and the trusty dateformat() function can be used to simply cludge your dates into compliance : )

 

[iza]

oh yes and use the setLocale

 

[gasparrelli]

Thanks all - but this was in fact verified as a bug by the folks at Allaire. 4.5.1 Enterprise has a date problem using CFstoredProc which is fixed by SP2 (we downloaded the beta) In our case it was specific to the Oracle native driver but it may be out there in other forms as well with 4.5.1.

Sad part was that it took Allaire about 5 days to recognize this as a known bug, which set out product launch back 5 days. I'm hopeful that if the CF community pushes harder we will get better on-line Bug info so we don't have to go through all of this!

 

[KevinFSI]

Dig it!!