cfqueryparam date greater than or equal to

Question

I am using Oracle and I have a select query where I want to select from the Inventory table with Year greater than or equal to year 2000.

Select * from Inventory
where 
manufac_date >=  <cfqueryparam value="01/01/2000" cfsqltype="CF_SQL_DATE">

I am getting only those values with year greater than 2000, but not those from year 2000.

Any comments or suggestions please?

Answer 1

Maybe Oracle is different. But as long as it recognizes cf_sql_date, and not just cf_sql_timestamp, I see no reason it should not work. Are you positive :

1. the table contains records for that period
2. those records are not being excluded by some other condition
3. that you are actually using >= and not just >

Answer 2

You should either make your cfqueryparam value a date object

manufacDateFilter = createDate(2000, 1 , 1);

in the query then you have

where
manufac_date >= <cfqueryparam value="#manufacDateFilter#" cfsqltype="CF_SQL_DATE">

or use Oracle's to_date function like so (not tested, but should work)

manufac_date >= to_date(<cfqueryparam cfsqltype="cf_sql_varchar" value="01/01/2000">,'mm/dd/yyyy')