>Does anybody know what differences there might be between the 4.5 and MX >ODBC drivers for a Oracle database. We have a query to the Oracle database >the runs just fine on a CF4.5 server, but when we migrated to new CFMX >server all of a sudden the query is throwing errors. Both cold fusion >servers are pointing the same Oracle database server. It seems to involve >a >date field. If we try to select the offending field from the MX we get >following error. > >Error Executing Database Query. >[MERANT][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC]Invalid datetime >format. > >I'm just wondering why the version running on the 4.5 server has no trouble >selecting this date field, but that the MX server does? > >Thanks for any information.
I'm not sure about the specific driver differences, but I do have a good amount of experience with Oracle and finds that it's very particular about dates. To fix this problem, I just use its built in functions to take care of formatting issues. Here are some examples: 1. Instead of using #Now()# as the date to insert, try using "sysdate" instead (without the quotes). You can see sysdate's value by running a simple query: SELECT sysdate FROM dual 2. When a particular date is necessary, use the to_date() Oracle function like so: to_date(<cfqueryparam value="#DateFormat(variables.myDate, "mm-dd-yyyy")#" cfsqltype="CF_SQL_VARCHAR">, 'mm-dd-yyyy') 3. If you need to insert a particular date *and* time, you can still use Oracle's to_date() function, like so: to_date(<cfqueryparam value="#DateFormat(variables.myDate, "mm-dd-yyyy")# #TimeFormat(variables.myTime, "HH:mm:ss")#" cfsqltype="CF_SQL_VARCHAR">, 'mm-dd-yyyy HH24:MI:SS') (You can use HH12:MI:SS is you want to use the 12-hour clock instead of the 24-hour clock format) If you look in Oracle's reference docs, you'll see that there are many different masks you can apply to the to_date() function, not just 'mm-dd-yyyy', so you can change that to your liking. Again, it's not an answer to your specific question about driver differences, but using the above two hints has not caused a problem for me, whether I'm using CF 4.5., 5, or MX (and I move in and out of all three regularly). Regards, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
