Oracle SQL Date Syntax

I created this application on my local machine using Access. Worked great. Uploaded it to the Unix box, where we use Oracle, and initially, it worked. But with the last set of data I loaded, it stopped working -- on the Unix box.  Works fine, still, locally with Access. The problem is with a query on dates. In Access, I'm using: SellDate BETWEEN CreateODBCDate(DateFormat(#form.StartDate#, "mm/dd/yyyy")) AND CreateODBCDate(DateFormat(#form.EndDate#, "mm/dd/yyyy")) In Oracle, I initially had working with: SellDate BETWEEN '#form.StartDate#' AND '#form.EndDate#' When that stopped working, I tried a number of variations, including: SellDate BETWEEN TO_DATE('#form.StartDate#', 'mm/dd/yyyy') AND TO_DATE('#form.EndDate#',  'mm/dd/yyyy') So, PLEASE, somebody -- what's the CORRECT syntax for this sort of query? Howard Owens Internet Operations Coordinator www.insidevc.com howens@insidevc.com AIM: GoCatGo1956 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm

I created this application on my local machine using Access. Worked great. Uploaded it to the Unix box, where we use Oracle, and initially, it worked. But with the last set of data I loaded, it stopped working -- on the Unix box.  Works fine, still, locally with Access. The problem is with a query on dates. In Access, I'm using (as part of the WHERE clause): SellDate BETWEEN #CreateODBCDate(DateFormat(form.StartDate, "mm/dd/yyyy"))# AND #CreateODBCDate(DateFormat(form.EndDate, "mm/dd/yyyy"))# In Oracle, I initially had working with: SellDate BETWEEN '#form.StartDate#' AND '#form.EndDate#' When that stopped working, I tried a number of variations, including: SellDate BETWEEN TO_DATE('#form.StartDate#', 'mm/dd/yyyy') AND TO_DATE('#form.EndDate#',  'mm/dd/yyyy') So, PLEASE, somebody -- what's the CORRECT syntax for this sort of query? Howard Owens Internet Operations Coordinator www.insidevc.com howens@insidevc.com AIM: GoCatGo1956 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm

Nevermind.  Problem solved. The problem was with the formatting of the date fields being passed to the query, not with the query itself. H. Howard Owens Internet Operations Coordinator www.insidevc.com howens@insidevc.com AIM: GoCatGo1956 ----- Excess quoted text cut - see Original Post for more ----- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm