Forum OpenACS Q&A: Using bind variables with Oracle dates

Request notifications

Posted by Brian Fenton on

Either I'm doing something wrong here, or else I've misunderstood it. Maybe someone could shed some light.

For a long time, I have been using something along the lines of the following for passing dates into SQL queries:
set start_date [template::util::date::get_property sql_date $start_date_param]
append where_clause " and ax.start_date >= $start_date "

However, I realised today that this isn't using bind variables, as the value of $start_date is to_date('2011 01 26', 'YYYY MM DD').

Am I using the wrong proc to prepare the date for the query? Wouldn't it need to do something like the following to properly use bind variables with Oracle? to_date(:the_start_date, :date_format)

I can handcode a solution, but I'm guessing there's already a right way to do this.

Any ideas?


Posted by Brian Fenton on
I figured out what was! In fact, I somehow knew the right way to do this but managed to confuse myself. The right thing to do is:

set start_date [template::util::date::get_property linear_date_no_time $start_date_param]
append where_clause " and ax.start_date >= to_date(:start_date, 'yyyy mm dd') "

Happy binding.