1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Converting a String to a Date using an after insert trigger.

Discussion in 'SQL PL/SQL' started by MFriend, Oct 13, 2014.

  1. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi Folks:

    I have a table that has a column (called DATE_FED) that must be saved in string format.

    After all the data is saved an after insert trigger is fired that copies certain columns to another table.

    The problem is that the after insert trigger will not work because the table being copied to stores the DATE_FED data in a Date format.

    I am trying to figure out how to use the TO_DATE function to convert the NEW.DATE_FED data to a Date format so the trigger will work.

    Here is the trigger as it currently reads:

    create or replace TRIGGER "FARM"."BALES_FED_PAST_TRIG" AFTER INSERT ON "FARM"."BALES_FED_PASTURE" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW Declare

    BEGIN

    insert into BALES_STORAGE (BALES_LOST_SUBTRACTED, CROP, CUTTING, STACKYARD, DATE_MOVED, FED_ID, BALE_YEAR) values :)new.BALES_FED, :new.CROP, :new.CUTTING, :new.STACKYARD, :new.DATE_FED, :new.FED_ID, :new.BALE_YEAR);

    END;

    This trigger worked before I had to change to column type of DATE_FED from Date to String.

    I have tried things like:
    TO_DATE(DATE_FED) := NEW.DATE_FED; in a declare statement but I'm not getting it.

    Summary:
    I need an after insert trigger to convert NEW.DATE_FED (a string) to NEW.DATE_FED (a Date).

    Tables:
    BALES_FED_PASTURE is the table the trigger is running on.
    BALES_STORAGE is the table the trigger is copying certain columns to.

    Thank you very much for any help.

    Matthew
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Matthew,

    You need to specify the appropriate format mask in your to_date statement - which depends in what format the DATE_FED is being inserted:
    for ex. :
    if DATE_FED='01-OCT-2014', then you have to use TO_DATE:)new.DATE_FED, 'DD-MON-YYYY')
    if DATE_FED='01-10-2014', then you have to use TO_DATE:)new.DATE_FED, 'DD-MM-YYYY')

    A quick example:

    Code (SQL):
    CREATE TABLE t1 (dt1 VARCHAR2(20));
    CREATE TABLE t2 (dt2 DATE);
    CREATE OR REPLACE TRIGGER t1_trg
    AFTER INSERT ON t1
    FOR EACH ROW
      DECLARE
      BEGIN
        INSERT INTO t2 (dt2) VALUES
          (to_date(:NEW.dt1, 'dd/mm/yyyy'));
      END;
      /

    INSERT INTO t1(dt1) VALUES ('13/10/2014');

    commit;

    SELECT * FROM t2;

    Output: (my DEFAULT DATE output format - NLS_DATE_FORMAT - being DD-MON-YYYY)
    DT2
    --------------
    13-OCT-2014
     
    MFriend likes this.
  3. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Thank you Rajen for your help, I appreciate it.

    It is working now : )

    The actual code if anyone is interested that worked is:
    create or replace TRIGGER "FARM"."BALES_FED_PAST_TRIG"
    AFTER INSERT ON "FARM"."BALES_FED_PASTURE"
    REFERENCING OLD AS "OLD" NEW AS "NEW"
    FOR EACH ROW
    BEGIN

    insert into BALES_STORAGE (BALES_LOST_SUBTRACTED, CROP, CUTTING, STACKYARD, DATE_MOVED, FED_ID, BALE_YEAR) values :)new.BALES_FED, :new.CROP, :new.CUTTING, :new.STACKYARD, TO_DATE:)new.DATE_FED,'mm/dd/yyyy'), :new.FED_ID, :new.BALE_YEAR);

    END;

    Thanks again,
    Matthew
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Thanks for the feedback Matthew. Glad to hear that it's working fine.

    Though you may have read or heard about it, just wanted to add that it's not a good practice to store dates as strings (VARCHAR) in the database. As I mentioned in my previous reply, the date format mask which you code in your trigger is dependent on the format of the 1st string inserted. The day this format changes the trigger will stop working and you'll have to modify your trigger (or whoever taking over this development or operations)
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To add to what has already been said storing dates as varchar2 strings also affects sort operations; the sort is now a collated sort rather than a numeric sort:


    Code (SQL):

    SQL>
    SQL> --
    SQL> -- Be silly and create a column containing dates as a character type
    SQL> --
    SQL> CREATE TABLE xx_fa_trxn_history_trx_v(period varchar2(100));


    TABLE created.


    SQL>
    SQL> --
    SQL> -- Load the 'date' data
    SQL> --
    SQL> INSERT ALL
      2  INTO xx_fa_trxn_history_trx_v
      3  VALUES('APR-12-13')
      4  INTO xx_fa_trxn_history_trx_v
      5  VALUES('MAY-12-13')
      6  INTO xx_fa_trxn_history_trx_v
      7  VALUES('JUN-12-13')
      8  INTO xx_fa_trxn_history_trx_v
      9  VALUES('JUL-12-13')
     10  INTO xx_fa_trxn_history_trx_v
     11  VALUES('JUL-13-14')
     12  INTO xx_fa_trxn_history_trx_v
     13  VALUES('APR-13-14')
     14  INTO xx_fa_trxn_history_trx_v
     15  VALUES('MAY-13-14')
     16  SELECT * FROM dual;


    7 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> --
    SQL> -- Create variables
    SQL> --
    SQL> variable P_PERIOD1 varchar2(12)
    SQL> variable P_PERIOD2 varchar2(12)
    SQL>
    SQL> BEGIN
      2          :P_PERIOD1:='APR-12-13';
      3          :P_PERIOD2:='JUN-12-13';
      4  END;
      5  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> --
    SQL> -- Now try to query as if they were dates
    SQL> --
    SQL>  SELECT *
      2   FROM xx_fa_trxn_history_trx_v
      3   WHERE 1=1  -- useless
      4   AND period BETWEEN :P_PERIOD1 --APR-12-13
      5   AND :P_PERIOD2 --JUN-12-13
      6  /


    PERIOD
    --------------------------------------------------------------------------------
    APR-12-13
    JUN-12-13
    JUL-12-13
    JUL-13-14
    APR-13-14


    SQL>
    SQL> --
    SQL> -- Doesn't work
    SQL> --
    SQL> -- Re-create the table with the proper data type
    SQL> --
    SQL> DROP TABLE xx_fa_trxn_history_trx_v purge;


    TABLE dropped.


    SQL> CREATE TABLE xx_fa_trxn_history_trx_v(period DATE);


    TABLE created.


    SQL>
    SQL> --
    SQL> -- Load the 'date' data
    SQL> --
    SQL> INSERT ALL
      2  INTO xx_fa_trxn_history_trx_v
      3  VALUES(to_date('APR-12-13', 'MON-DD-RR'))
      4  INTO xx_fa_trxn_history_trx_v
      5  VALUES(to_date('MAY-12-13', 'MON-DD-RR'))
      6  INTO xx_fa_trxn_history_trx_v
      7  VALUES(to_date('JUN-12-13', 'MON-DD-RR'))
      8  INTO xx_fa_trxn_history_trx_v
      9  VALUES(to_date('JUL-12-13', 'MON-DD-RR'))
     10  INTO xx_fa_trxn_history_trx_v
     11  VALUES(to_date('JUL-12-14', 'MON-DD-RR'))
     12  INTO xx_fa_trxn_history_trx_v
     13  VALUES(to_date('APR-13-14', 'MON-DD-RR'))
     14  INTO xx_fa_trxn_history_trx_v
     15  VALUES(to_date('MAY-13-14', 'MON-DD-RR'))
     16  SELECT * FROM dual;


    7 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> --
    SQL> -- Populate variables
    SQL> --
    SQL> BEGIN
      2          :P_PERIOD1:=to_date('APR-12-13','MON-DD-RR');
      3          :P_PERIOD2:=to_date('JUN-12-13','MON-DD-RR');
      4  END;
      5  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> --
    SQL> -- Now query the dates
    SQL> --
    SQL>  SELECT *
      2   FROM xx_fa_trxn_history_trx_v
      3   WHERE 1=1  -- useless
      4   AND period BETWEEN :P_PERIOD1 --APR-12-13
      5   AND :P_PERIOD2 --JUN-12-13
      6  /


    PERIOD
    ---------
    12-APR-13
    12-MAY-13
    12-JUN-13


    SQL>
     

    Dates are supposed to remain as dates, not strings.