Have Geek Brain

Oracle sql: using a binding variable for dates

Here is a simple working query without bind variables:

select * from table1 where time_stamp > sysdate - INTERVAL '1' day;

where time_stamp

has a type DATE

.

I should be able to enter any number of days in the above query using a binding variable.

So, I tried the following and it doesn't work:

select * from table1 where time_stamp > sysdate - INTERVAL :days day;

I tried to enter numeric input both 10 and 10 for example. You are getting ORA-00933 error on 10g.

+2


a source to share


1 answer


The string INTERVAL '1' day

in the original query is a literal range, i.e. it is calculated by the parser for one value. You cannot replace part of it with a binding variable.



If you use NUMTODSINTERVAL( 1, 'DAY' )

then 1 is an integer literal which you can replace with a binding variable.

+9


a source






More articles:


All Articles