I have this query that I can't seem to get it right. Maybe I'm not thinking clearly, or maybe, I'm just not understanding dates and between function. Hope someone can help me ...
I have a FORM with 2 text fields for the users' to enter the dates they want. The users can enter *060612* and it'll become *06/06/12*. So on the on click button, I have the following codes.
select * from abc where
(
to_date(trim(paydate), 'rrrrmmdd') BETWEEN
to_date(to_date(:control.date1,'mm/dd/yy'),'rrrrmmdd') and
to_date(to_date(:control.date2,'mm/dd/yy'),'rrrrmmdd')
)
order by employee_id;
As mentioned, *:control.date1* and *:control.date2* should be *06/06/12* and
paydate field is varchar(8) and data entered as *'yyyymmdd'*.
I am getting error
ORA-01858: a non-numeric character was found where a numeric was expected*
What am I missing?