Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Functional Index on date Column getting Err ORA 1841

    hi
    for this statment

    SELECT MAX(EFFDT) FROM PS_DEPT_TBL
    WHERE SETID = DTS.SETID AND
    DEPTID = DTS.DEPTID AND
    -- EFFDT <= '17-SEP-2010')

    I tried to create an index as below: but I get an error: "ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    "
    create index effdt_dx on PS_DEPT_TBL(to_date(effdt, 'DD-MON-YYYY'));

    I get the similar when I do a select as below:

    select to_date(effdt, 'DD-MON-YYYY') from PS_DEPT_TBL;

    But if I select without to_date then no errors.

    Please Advise
    Priya

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What datatype is the column effdt (ideally post the complete CREATE TABLE statement for PS_DEPT_TBL)
    Show us some sample data for the column effdt

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    desc PS_DEPT_TBL

    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SETID NOT NULL VARCHAR2(5)
    DEPTID NOT NULL VARCHAR2(10)
    EFFDT NOT NULL DATE

    so Ithought of changing

    then I change EFFDT <= '17-SEP-2010') to EFFDT <= to_date('09/17/2010','MM/DD/YYYY'))

    but still no success..

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    to_date() converst a character string into a date.

    You cannot use it on a column that is already of type DATE

    Most probably you are not telling us the whole story, but I fail to see why you need a function based index.

    What's wrong with:
    Code:
    create index effdt_dx on PS_DEPT_TBL(effdt);
    then I change EFFDT <= '17-SEP-2010') to EFFDT <= to_date('09/17/2010','MM/DD/YYYY'))
    What do you mean with "I change"?
    The above expression is not valid for an index expression.

    What are you really trying to achieve?

Posting Permissions

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