2 Replies Latest reply: Aug 24, 2013 5:59 PM by user11872870 RSS

    How to calculate the load time, max -min date Query

    user11872870
        CREATE TABLE TEST ( GID VARCHAR2(100 BYTE), SGID VARCHAR2(100 BYTE), PID VARCHAR2(100 BYTE), DATES TIMESTAMP (6) );
      
      REM INSERTING into TEST
      SET DEFINE OFF;
      Insert into TEST (GID,SGID,PID,DATES) values ('1','1000','ABC',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into TEST (GID,SGID,PID,DATES) values ('1','1001','BCD',to_timestamp('24-AUG-13 05.21.46.491000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into TEST (GID,SGID,PID,DATES) values ('1','1002','CDE',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into TEST (GID,SGID,PID,DATES) values ('2','1004','EDF',to_timestamp('23-AUG-13 05.22.20.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into TEST (GID,SGID,PID,DATES) values ('2','1003','FEG',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into TEST (GID,SGID,PID,DATES) values ('2','1001','GHI',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into TEST (GID,SGID,PID,DATES) values ('2','1006','JKL',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into TEST (GID,SGID,PID,DATES) values ('3','1007','LMN',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
      Insert into TEST (GID,SGID,PID,DATES) values ('3','1001','OPQ',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));
      

       

      Hi,

       

      I need a sql query which gives me the time took to load the records ie., Max date minus Min date. I have ran a below query which gives me the "0 23:59:26.491" result.

       

      select (max(DATES) - min(DATES)) from test;
      

       

      When I apply the same logic in Peoplesoft enterprise manager, It is generating the below query and throwing me the "ORA-01722: invalid number" error.

       

      select max(TO_CHAR(cast((DATES) as timestamp),'YYYY-MM-DD-HH24.MI.SS.FF'))
      -min(TO_CHAR(cast((DATES) as timestamp),'YYYY-MM-DD-HH24.MI.SS.FF'))
      from test; 
      

       

      Can you please modify the above query?

        • 1. Re: How to calculate the load time, max -min date Query
          sb92075

          user11872870 wrote:

           

          1.   CREATE TABLE TEST ( GID VARCHAR2(100 BYTE), SGID VARCHAR2(100 BYTE), PID VARCHAR2(100 BYTE), DATES TIMESTAMP (6) ); 
          2. REM INSERTING into TEST 
          3. SET DEFINE OFF
          4. Insert into TEST (GID,SGID,PID,DATES) values ('1','1000','ABC',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM')); 
          5. Insert into TEST (GID,SGID,PID,DATES) values ('1','1001','BCD',to_timestamp('24-AUG-13 05.21.46.491000000 PM','DD-MON-RR HH.MI.SS.FF AM')); 
          6. Insert into TEST (GID,SGID,PID,DATES) values ('1','1002','CDE',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM')); 
          7. Insert into TEST (GID,SGID,PID,DATES) values ('2','1004','EDF',to_timestamp('23-AUG-13 05.22.20.000000000 PM','DD-MON-RR HH.MI.SS.FF AM')); 
          8. Insert into TEST (GID,SGID,PID,DATES) values ('2','1003','FEG',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM')); 
          9. Insert into TEST (GID,SGID,PID,DATES) values ('2','1001','GHI',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM')); 
          10. Insert into TEST (GID,SGID,PID,DATES) values ('2','1006','JKL',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM')); 
          11. Insert into TEST (GID,SGID,PID,DATES) values ('3','1007','LMN',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM')); 
          12. Insert into TEST (GID,SGID,PID,DATES) values ('3','1001','OPQ',to_timestamp('24-AUG-13 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM')); 

           

          Hi,

           

          I need a sql query which gives me the time took to load the records ie., Max date minus Min date. I have ran a below query which gives me the "0 23:59:26.491" result.

           

          1. select (max(DATES) - min(DATES)) from test; 

           

          When I apply the same logic in Peoplesoft enterprise manager, It is generating the below query and throwing me the "ORA-01722: invalid number" error.

           

          1. select max(TO_CHAR(cast((DATES) as timestamp),'YYYY-MM-DD-HH24.MI.SS.FF')) 
          2. -min(TO_CHAR(cast((DATES) as timestamp),'YYYY-MM-DD-HH24.MI.SS.FF')) 
          3. from test;  

           

          Can you please modify the above query?

           

          1. select max(cast(DATES) as date) - mIN(cast(DATES) as date) from test; 
          • 2. Re: How to calculate the load time, max -min date Query
            user11872870

            Awesome, Thank you so much sir.