> When I run the following query in SQLPlus, any row with a > 'valid_to_date' > older than today's date is excluded. But when I run the > same query in CF, > they are included. Why? > ~~ CF 5 on Win2K hitting Oracle 8i on IBM Mainframe ~~ > valid_to_date is a date field, not varChar2
> SELECT ID, course_prefix, course_number, course_title, > valid_to_date > FROM siswarehouse.course_description > WHERE course_prefix LIKE 'W%' AND > (valid_to_date IS NULL OR > valid_to_date = '' OR > valid_to_date > '02-Apr-2003') > ORDER BY course_prefix, course_number Probably CF is using a different set of drivers than SQLPlus ... The second valid_to_date line shouldn't be necessary... afaik a date column will never = '' -- it's either NULL or it's a date. You might also try using <cfqueryparam> in your CF it might make all the dif' in the world... valid_to_date > <cfqueryparam value="#now()#" cfsqltype="cf_sql_date"> You could also try valid_to_date > '#createodbcdate(mydate)#' since it's possible the Oracle drivers CF is using may not like the non-sql standard date format. Though I'd opt for the cfqueryparam option first since it's going to be the most reliable if it works. hth s. isaac dealey 954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89 70.4
