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

1: Using bind variables with Oracle dates

Posted by Brian Fenton on 01/26/12 04:11 PM

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?

thanks
Brian

2: Re: Using bind variables with Oracle dates (response to 1)

Posted by Brian Fenton on 01/26/12 05:19 PM

I figured out what was wrong....me! 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.