This is my first post to this forum so please be gentle with me.
Here's my problem:
I have created a view that contains a date Colum that has been populated from a varchar and therefore has to use the TO_Date() function. That all works fine.
I then wish to select against the view for all records over the last 36 months. To do this I use Add_Months( sysdate, -36). When I use this as the where clause I get the message "ORA-01861: literal does not match format string"
I've tried all sorts of variations with the view creation and the select where clause and it's making my brain hurt.
Here is the code I'm using for creating the view and selecting from it.
CREATE OR REPLACE FORCE VIEW "TRENTADM"."PC_VARIABLE_V" ("PERSON_ID", "EFFECTIVE_DATE") AS
A.KEY_ITEM AS PERSON_ID,
TO_DATE(B.DEF_FLD_VL, 'DD-MM-YYYY') AS EFFECTIVE_DATE
INNER JOIN trentadm.TUER_USR_DEF_FLD_V B ON A.def_grp_vl_id = B.def_grp_vl_id
(A.FIELD_GROUP_DESC = 'Contribution salary')
AND (B.FIELD_DESC = 'Effective date')
AND (B.DEF_FLD_VL IS NOT NULL)
select * from PC_VARIABLE_V where Effective_Date > TO_Date(add_months( sysdate, -36), 'DD-MM-YYYY')
No, when you create the view Oracle doesn't validate all the data for you. If it did, creating a view against a large table could take hours! The conversion gets done for the very first time whe you SELECT from the view:
SQL> create table t1 (data varchar2(20));
SQL> create view v1 as select to_date(data,'DD/MM/YYYY') dateval from t1;
SQL> insert into t1 (data) values ('xxxxx');
1 row created.
SQL> select * from v1;
select * from v1
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected