Solved

ORA-01861: literal does not match format string

Posted on 2003-11-10
4
79,286 Views
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:

BEGIN
     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;  
END;
/


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.

Thanks
0
Comment
Question by:baonguyen1
[X]
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
4 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 150 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')

0
 
LVL 8

Author Comment

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

It works now.

Cheers,
0
 
LVL 48

Expert Comment

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

Author Comment

by:baonguyen1
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.

Cheers,

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
create a nested synonym 4 41
Oracle encryption 12 60
SYS password changed. Now can't log in as SYS 27 43
Procedure syntax 5 50
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 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