2 Replies Latest reply: Jul 4, 2012 4:41 AM by Javid Hesenov RSS

    Date With timestamp in TRIGGER

    926362
      i m creating the dynamic table every month to maintain the particular month data seperately .when the records getting inserted in the table,trigger will automatically insert the records in the dynamic table. only date alone(without timestamp) getting inserted in the dynamic table from staging. so by default ,00:00:00 is getting appended with date instead of actual timestamp. tried select to_date(to_char(:new.ACTN_DATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') INTO v_temp_actn_date  from dual; but i am getting only date alone . in my table and dynmaic table datatype for date column is date . please help me solve this issue .Thanks.
        • 1. Re: Date With timestamp in TRIGGER
          damorgan
          Please read:
          http://en.wikipedia.org/wiki/Database_normalization
          http://www.bkent.net/Doc/simple5.htm

          Unless I am totally misunderstanding what you have written ... this is the poster-child project for a bad design. I recommend you stop work and reconsider.
          • 2. Re: Date With timestamp in TRIGGER
            Javid Hesenov
            I am misunderstanding your question but i think this will help you. Try:

            alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

            Also instead of creating dynamic table to maintain particular month data, use table range partitioning on date column. Then you can simply select rows for particular month with:

            select * from your_table partition (partition_name_for_required_month);

            This will prevent duplicating of data and increase query performance when searching for particular period.

            Best Regards

            Javid Hasanov