Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need Help in Date conversion

2975076Jun 19 2015 — edited Jun 19 2015

Hi All,

  Your Input expertise is very much appreciated.

   Have an issue where this is my Master table structure, whenever there is a update/delete/insert on the master table
   there is a trigger that insert a entry into Audit table.
  
   Sql > Desc cpp_Master;
            Ent_id Number,
           Ent_date date
  
   Sql > Desc Cpp_Master_Audit
             Ent_id Number,
             Ent_date date,
             Insert_type varchar2(1)
  
   The problem is that when we insert the below record into table the ent_date column appears this way.
  
Sql > INSERT INTO CED.cpp_Master (ENT_ID, Ent_date)
VALUES (6030902, TO_DATE ('01/01/0001 00:00:00', 'MM/DD/YYYY HH24:MIS'));

Sql > SELECT * FROM CED.cpp_Master;

6030902 01/01/0001

Sql > SELECT * FROM CED.Cpp_Master_Audit;

6030902 01/01/0001

But when i update the date is coming up as below

Sql > update ced.cpp_Master set Ent_date = to_date('0001-MAR-01', 'YYYY-mon-dd') where ent_id='6030902';
Sql > Commit;

Tried below updates as well

(

update ced.cpp_Master set Ent_date = '0001-MAR-01' where ent_id='6030902';

update ced.cpp_Master set Ent_date = to_char('0001-MAR-01', 'YYYY-mon-dd') where ent_id='6030902';

)

Every time the output as below always. (Insert has no problem only update has problem)

I wonder why when there is a insert the value of date comes up as "01/01/0001" in (Cpp_Master_Audit), but when i update the value of date comes up "01/01/2001"

I need the value to be inserted as "01/01/0001", as whatever been updated in Master table(Cpp_Master)

Sql > SELECT * FROM CED.cpp_Master;

6030902 01/01/2001



My trigger query:

CREATE OR REPLACE TRIGGER CED.CPP_AUDT
AFTER INSERT OR UPDATE OR DELETE
   ON CED.cpp_Master    REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
   l_action_code         VARCHAR2 (1);
BEGIN

   IF INSERTING
   THEN
      l_action_code := 'I';
   ELSIF UPDATING
   THEN
      l_action_code := 'U';
   ELSE
      l_action_code := 'D';
   END IF;

   IF (   l_action_code = 'I'
       OR l_action_code = 'D'
       OR (    l_action_code = 'U'
           AND (   NVL (:OLD.ENT_ID, 0) <> NVL (:NEW.ENT_ID, 0)
                OR NVL (TO_CHAR (:OLD.Ent_date, 'mm/dd/yyyy'),
                        'X') <>
                      NVL (TO_CHAR (:NEW.Ent_date, 'mm/dd/yyyy'),
                           'X')
                )))
   THEN
       INSERT INTO CED.Cpp_Master_Audit (
                                       ENT_ID,Ent_date,Insert_type
                                      )
       VALUES (DECODE (:OLD.ent_id, NULL, :NEW.ent_id, :OLD.ent_id),
               DECODE (l_action_code,'I',:NEW.Ent_date,'D',NULL,'U',DECODE (:NEW.Ent_date,:OLD.Ent_date, NULL,:NEW.Ent_date)),
      Insert_type l_action_code);

   END IF;
END;
/

This post has been answered by RogerT on Jun 19 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 17 2015
Added on Jun 19 2015
15 comments
1,743 views