Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: To_Date and Add_Months problem

    Hi There.
    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.
    Code:
      CREATE OR REPLACE FORCE VIEW "TRENTADM"."PC_VARIABLE_V" ("PERSON_ID", "EFFECTIVE_DATE") AS 
      SELECT
                A.KEY_ITEM AS PERSON_ID,
                TO_DATE(B.DEF_FLD_VL, 'DD-MM-YYYY') AS EFFECTIVE_DATE
         FROM
                trentadm.TUER_USR_DEF_FLD_V A 
                INNER JOIN trentadm.TUER_USR_DEF_FLD_V B ON A.def_grp_vl_id = B.def_grp_vl_id
         WHERE
                (A.FIELD_GROUP_DESC = 'Contribution salary') 
                AND (B.FIELD_DESC = 'Effective date')
                AND (B.DEF_FLD_VL IS NOT NULL)
    Code:
    select * from PC_VARIABLE_V where Effective_Date > TO_Date(add_months( sysdate, -36), 'DD-MM-YYYY')
    Thanks in advance for your help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    ADD_MONTHS returns a DATE not a VARCHAR2, so you should not apply a TO_DATE to the result:
    Code:
    select * from PC_VARIABLE_V where Effective_Date > add_months( sysdate, -36)

  3. #3
    Join Date
    Apr 2006
    Posts
    5
    You get the same error whether you use To_Date on the Add_Months function or not.

    That was just my sample code I'd posted. Like I said, I've tried everything I can think of.

    Thanks anyway.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, don't use TO_DATE anyway because it is wrong!

    Perhaps there is data in DEF_FLD_VL that is not in the format DD-MM-YYYY?

  5. #5
    Join Date
    Apr 2006
    Posts
    5
    DEF_FLD_VL is a text field, hence the need to use To_Date when creating the view. I would have expected an error when creating the view if there was a problem converting it to a date.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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:
    Code:
    SQL> create table t1 (data varchar2(20));
    
    Table created.
    
    SQL> create view v1 as select to_date(data,'DD/MM/YYYY') dateval from t1;
    
    View created.
    
    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

  7. #7
    Join Date
    Apr 2006
    Posts
    5
    I have eventually solved this problem by creating a materialized view.

    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •