CODEWITHSUNDEEP
oracle-databasecoldfusion
Fransis
Fransis

Reputation: 285

cfqueryparam date greater than or equal to

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?

Upvotes: 3

Views: 2299

Answers (2)

Andreas Schuldhaus
Andreas Schuldhaus

Reputation: 2648

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')

Upvotes: 1

Leigh
Leigh

Reputation: 28873

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 >

Upvotes: 1

Related Questions