Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


ORA-01861: literal does not match format string

Posted on 2003-11-10
Medium Priority
Last Modified: 2011-08-18
Hi Gurus,

I have a table WO as:

SQL> Desc WO
actstart    date
actfinish   date
estdur     float
wonum    varchar2(10)

In a package, I declare:

   type actstart_tab_type is table of date index by binary_integer;
   actstart_tab     actstart_tab_type;

What I want to do it calculate the ACTSTART(date) by using  ACTFINISH (date)  minus ESTDUR (h)
In one of the triggres:

     IF :new.status = 'COMP' THEN
      wo_act_time_pkg.actstart_tab(wo_act_time_pkg.actstart_tab.COUNT+1) :=
      to_date((:new.actfinish-:old.estdur/24),'dd-Mon-yy HHMI');  
     END IF;  

and then in other:

UPDATE WO  SET ACTSTART= (wo_act_time_pkg.actstart_tab(i)
        where WONUM = wo_act_time_pkg.wo_tab(i);

I  got ORA-01861: literal does not match format string when the first trigger was fired. Tried with many TO_DATE format but could not solve problem. Oracle 8163

Please help.

Question by:baonguyen1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 48

Accepted Solution

schwertner earned 450 total points
ID: 9715310
to_date((:new.actfinish-:old.estdur/24),'dd-Mon-yy HHMI') has no meaning. It is DATE!!!!

(:new.actfinish-:old.estdur/24)   - use this as date.

If you are curious about the value look at the result so:

to_char((:new.actfinish-:old.estdur/24),'dd-Mon-yy HHMI')


Author Comment

ID: 9719985
Thanks  schwertner. It was my big mistake.

It works now.

LVL 48

Expert Comment

ID: 9721395
Thanks baonguyen1!
But why you grade the answer with B?
You got the truth, I am not guilty that you were surprised!

Author Comment

ID: 9722300
I did not aware that I just grade with B. Can I have chance to reverse to A ?

Thanks a lot for your help and sorry for a B grade.



Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question