CFMX7 & informix Date conversion woes

[CheapEnergy]

I have a trial install of CF MX7 w/ informix 9.3 on linux. Everything works
fine until I query using dates.
The front-end MUST allow users to query in "MM/DD/YY" format.
When I submit a cfm page it dies with the error - "ERROR executing Database
Query...string to date conversion error"
However the same page works okay if the date is submitted in the format
"YYYY-MM-DD'
I tried setting the env variable DBDATE, but that has not helped.
Any thoughts?

[Dan Bracuk]

Write some code to format the date so that you can make it play with your db.

Originally posted by: CheapEnergy
I have a trial install of CF MX7 w/ informix 9.3 on linux. Everything works
fine until I query using dates.
The front-end MUST allow users to query in "MM/DD/YY" format.
When I submit a cfm page it dies with the error - "ERROR executing Database
Query...string to date conversion error"
However the same page works okay if the date is submitted in the format
"YYYY-MM-DD'
I tried setting the env variable DBDATE, but that has not helped.
Any thoughts?

[CheapEnergy]

The correct way is to use the new 3.5 JDBC driver and to set the DBDATE
envrionment variable in the connection string


However I am having problems doing this.

I put : DBDATE=MDY4 in the connection string field and restarted CF,
That did not fix the problem. But it did erase the connection string settings

Does anyone know the correct way to set the connection string?

[CheapEnergy]

In case others face this problem , here is what I had to do:

Delete the data source in cold fusion administrator as cold fusion does not
retain connection string parameters after a restart.
Recreate the data source in Cold Fusion Administrator, and specify
DBDate=MDY4 or the format you need.
Voila ! it works.