dont know about best practice, but to ensure date portability I dont use date fields in the DB, I use varchar fields and put ISO dates in them e.g.
20051027T161420 Then if needed, I convert to proper dates once the value is out of the DB. Since I know the string will alway be in the same format, I can use string functions e.g. mid() to pull the data out and create a date - createdate(left(mydate,4),mid(mydate,5,2) e.t.c. -----Original Message----- From: wolf2k5 [mailto:[EMAIL PROTECTED] Sent: 27 October 2005 15:56 To: CF-Talk Subject: Re: portable SQL queries with date and time values On 8/17/05, wolf2k5 <[EMAIL PROTECTED]> wrote: > 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. Hi there, I didn't find a good reply to my previous questions yet. What is the best practice here in your opinion? Thanks a lot. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:222431 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
