Hi, I am trying to figure out what is the best way to write SQL queries including date and time values that will work across several Oracle Database installations (different versions and different regional settings).
So far I am using some code similar to this: <cfset myMask = "MM/DD/YYYY"> <!--- just for testing, the real value is not the current date ---> <cfset myDate = DateFormat(Now(),myMask)> <cfquery name="myQuery" datasource="myDatasource"> SELECT * FROM myTable WHERE TRUNC(myDate) < TO_DATE(<cfqueryparam value="#myDate#" cfsqltype="CF_SQL_VARCHAR">,<cfqueryparam value="#myMask#" cfsqltype="CF_SQL_VARCHAR">) </cfquery> It works fine and AFAIK it should be pretty portable to other Oracle Database installations. But I just figured out that simpler code also seems to work fine: <cfset myDate = DateFormat(Now(),myMask)> <cfquery name="myQuery" datasource="myDatasource"> SELECT * FROM myTable WHERE TRUNC(myDate) > <cfqueryparam value="#myDate#" cfsqltype="CF_SQL_DATE"> </cfquery> I'd like to use it since it's more compact ... Will it work fine with all Oracle Database installations? Also, I found some posts recommending to use the CreateODBCDate function. Is that a better way? Why? Thanks. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215432 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
