Loading ...
Sorry, an error occurred while loading the content.

Re: [PkOug] Time Calculate Problem

Expand Messages
  • Hafeez Imran
    Dear Zahid, If you want to get exact results, you should have datetime column in your table. But now you should be tricky, In order to get time difference use
    Message 1 of 7 , Feb 18 2:12 AM
    • 0 Attachment
      Dear Zahid,

      If you want to get exact results, you should have datetime column in your table. But now you should be tricky, In order to get time difference use to_date function convert your string into datetime and then check result.
       
      Best Regards
      Imran Hafeez
      Assistant Manager IT
      The Institute of Chartered Accountants of Pakistan
      Phone No: 99-251635
      Mobile No: 0300-2299749
      Email: manirose@..., famnis@...

      From: zahid <zaidi_005@...>
      To: PkOug@yahoogroups.com
      Sent: Saturday, February 16, 2013 11:14 AM
      Subject: [PkOug] Time Calculate Problem

       
      Aslamualakum,

      I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:

      T1   varchar2(5);
       T2   varchar2(5); 
       Diff  varchar2(5);  

                                           
      T1T2Calculate  Difrrence time
      07:3008:10
      07:3008:00
      07:3008:08
      Total??


      Regards



      Zahid Waseem


    • zahid
      Thanks i will try  Regards Zahid Waseem ... From: Hafeez Imran Subject: Re: [PkOug] Time Calculate Problem To: PkOug@yahoogroups.com
      Message 2 of 7 , Feb 19 12:32 AM
      • 0 Attachment
        Thanks i will try 

        Regards



        Zahid Waseem

        --- On Mon, 2/18/13, Hafeez Imran <famnis@...> wrote:

        From: Hafeez Imran <famnis@...>
        Subject: Re: [PkOug] Time Calculate Problem
        To: "PkOug@yahoogroups.com" <PkOug@yahoogroups.com>
        Date: Monday, February 18, 2013, 3:12 PM

         
        Dear Zahid,

        If you want to get exact results, you should have datetime column in your table. But now you should be tricky, In order to get time difference use to_date function convert your string into datetime and then check result.
         
        Best Regards
        Imran Hafeez
        Assistant Manager IT
        The Institute of Chartered Accountants of Pakistan
        Phone No: 99-251635
        Mobile No: 0300-2299749
        Email: manirose@..., famnis@...

        From: zahid <zaidi_005@...>
        To: PkOug@yahoogroups.com
        Sent: Saturday, February 16, 2013 11:14 AM
        Subject: [PkOug] Time Calculate Problem

         
        Aslamualakum,

        I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:

        T1   varchar2(5);
         T2   varchar2(5); 
         Diff  varchar2(5);  

                                             
        T1T2Calculate  Difrrence time
        07:3008:10
        07:3008:00
        07:3008:08
        Total??


        Regards



        Zahid Waseem


      • wasim.awans
        Dear, CREATE OR REPLACE FUNCTION SF_GET_PROD_STOPPAGE (V_TIMEIN IN VARCHAR2, V_TIMEOUT IN VARCHAR2) RETURN VARCHAR2 AS V_STOPPAGE VARCHAR2(6); V_ST_HR
        Message 3 of 7 , Feb 22 9:35 PM
        • 0 Attachment
          Dear,

          CREATE OR REPLACE FUNCTION "SF_GET_PROD_STOPPAGE"
          (V_TIMEIN IN VARCHAR2,
          V_TIMEOUT IN VARCHAR2)
          RETURN VARCHAR2 AS
          V_STOPPAGE VARCHAR2(6);
          V_ST_HR varchar2(6);
          V_ST_MI varchar2(6);

          BEGIN

          V_ST_HR := EXTRACT(HOUR FROM (TO_TIMESTAMP(TO_CHAR(V_TIMEOUT, '00.00'), 'HH24:MI') - TO_TIMESTAMP(TO_CHAR (V_TIMEIN, '00.00'), 'HH24:MI')));
          V_ST_MI := EXTRACT(MINUTE FROM (TO_TIMESTAMP(TO_CHAR(V_TIMEOUT, '00.00'), 'HH24:MI') - TO_TIMESTAMP(TO_CHAR (V_TIMEIN, '00.00'), 'HH24:MI')));

          V_STOPPAGE := LTRIM(TO_CHAR(V_ST_HR, '00')) || '.' || LTRIM(TO_CHAR(V_ST_MI, '00'));

          RETURN TRIM(V_STOPPAGE);
          EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
          RETURN NULL;
          END SF_GET_PROD_STOPPAGE;


















          --- In PkOug@yahoogroups.com, zahid <zaidi_005@...> wrote:
          >
          > Thanks i will try 
          >
          > Regards
          >
          >
          >
          >
          >
          >
          >
          > Zahid Waseem
          >
          > --- On Mon, 2/18/13, Hafeez Imran <famnis@...> wrote:
          >
          > From: Hafeez Imran <famnis@...>
          > Subject: Re: [PkOug] Time Calculate Problem
          > To: "PkOug@yahoogroups.com" <PkOug@yahoogroups.com>
          > Date: Monday, February 18, 2013, 3:12 PM
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >  
          >
          >
          >
          >
          >
          >
          >
          >
          >
          > Dear Zahid,
          >
          > If you want to get exact results, you should have datetime column in your table. But now you should be tricky, In order to get time difference use to_date function convert your string into datetime and then check result.
          >  Best Regards
          > Imran Hafeez
          > Assistant Manager IT
          > The Institute of Chartered Accountants of Pakistan
          > Phone No: 99-251635
          > Mobile No: 0300-2299749
          > Email: manirose@..., famnis@...
          > From: zahid <zaidi_005@...>
          > To: PkOug@yahoogroups.com
          > Sent: Saturday, February 16, 2013 11:14 AM
          > Subject: [PkOug] Time Calculate Problem
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >  
          >
          >
          >
          >
          >
          >
          >
          >
          >
          > Aslamualakum,
          > I have two columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:
          > T1   varchar2(5); T2   varchar2(5);  Diff  varchar2(5);  
          >                                      
          >
          >
          >
          >
          >
          >
          >
          >
          >
          > T1T2Calculate  Difrrence time
          >
          >
          >
          > 07:30
          > 08:10
          >
          >
          >
          >
          >
          > 07:30
          > 08:00
          >
          >
          >
          >
          >
          > 07:30
          > 08:08
          >
          >
          >
          >
          > Total
          > ?
          > ?
          >
          >
          >
          >
          >
          >
          >
          >
          > Regards
          >
          >
          >
          >
          >
          >
          >
          > Zahid Waseem
          >
        • M Kawish Siddiqui
          Isn’t it pretty simple? check below SQL, you won’t need to have additional UDF. SELECT TO_CHAR(TO_TIMESTAMP(OUTTIME, HH24:MI ) - TO_TIMESTAMP(INTIME,
          Message 4 of 7 , Feb 26 7:21 AM
          • 0 Attachment
            Isn’t it pretty simple? check below SQL, you won’t need to have additional UDF.
             
            SELECT TO_CHAR(TO_TIMESTAMP(OUTTIME, 'HH24:MI') - TO_TIMESTAMP(INTIME, 'HH24:MI'),'HH24.MI')
            FROM (SELECT '23.00' INTIME, '23.30' OUTTIME FROM DUAL);
             
            Your SQL Can be pretty straight like this:
             
            SELECT TO_CHAR(TO_TIMESTAMP(YOUR_OUTTIME, 'HH24:MI') - TO_TIMESTAMP(YOUR_INTIME, 'HH24:MI'),'HH24.MI')
            FROM YOUR_TABLE;
             

            BR,
            Kawish Siddiqui -
            +1 832 708 6862
             
            Sent: Friday, February 22, 2013 11:35 PM
            Subject: [PkOug] Re: Time Calculate Problem
             
             


            Dear,

            CREATE OR REPLACE FUNCTION "SF_GET_PROD_STOPPAGE"
            (V_TIMEIN IN VARCHAR2,
            V_TIMEOUT IN VARCHAR2)
            RETURN VARCHAR2 AS
            V_STOPPAGE VARCHAR2(6);
            V_ST_HR varchar2(6);
            V_ST_MI varchar2(6);

            BEGIN

            V_ST_HR := EXTRACT(HOUR FROM (TO_TIMESTAMP(TO_CHAR(V_TIMEOUT, '00.00'), 'HH24:MI') - TO_TIMESTAMP(TO_CHAR (V_TIMEIN, '00.00'), 'HH24:MI')));
            V_ST_MI := EXTRACT(MINUTE FROM (TO_TIMESTAMP(TO_CHAR(V_TIMEOUT, '00.00'), 'HH24:MI') - TO_TIMESTAMP(TO_CHAR (V_TIMEIN, '00.00'), 'HH24:MI')));

            V_STOPPAGE := LTRIM(TO_CHAR(V_ST_HR, '00')) || '.' || LTRIM(TO_CHAR(V_ST_MI, '00'));

            RETURN TRIM(V_STOPPAGE);
            EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
            RETURN NULL;
            END SF_GET_PROD_STOPPAGE;



            --- In mailto:PkOug%40yahoogroups.com, zahid wrote:

            >
            > Thanks i will tryÂ
            >
            > Regards
            >
            >
            >
            >
            >
            >
            >
            > Zahid
            Waseem
            >
            > --- On Mon, 2/18/13, Hafeez Imran wrote:
            >
            > From: Hafeez Imran
            > Subject: Re: [PkOug] Time Calculate
            Problem
            > To: "
            href="mailto:PkOug%40yahoogroups.com">mailto:PkOug%40yahoogroups.com" mailto:PkOug%40yahoogroups.com>
            >
            Date: Monday, February 18, 2013, 3:12 PM
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > Â
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > Dear Zahid,
            >
            > If you want to get exact results, you should have datetime column in
            your table. But now you should be tricky, In order to get time difference use to_date function convert your string into datetime and then check result.
            > Â Best Regards
            > Imran Hafeez
            > Assistant Manager
            IT
            > The Institute of Chartered Accountants of Pakistan
            > Phone No:
            99-251635
            > Mobile No: 0300-2299749
            > Email: manirose@...,
            famnis@...
            > From: zahid
            > To:
            href="mailto:PkOug%40yahoogroups.com">mailto:PkOug%40yahoogroups.com
            > Sent: Saturday, February 16, 2013 11:14 AM
            > Subject: [PkOug]
            Time Calculate Problem
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > Â
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > Aslamualakum,
            > I have two
            columns  having varchar2 data type , holding time data i want to calculate total hours,minutes and difference between in hours and min, using Oracle 11g Please. how i can calculate this:
            > T1   varchar2(5); T2  
            varchar2(5);  Diff  varchar2(5); Â
            >      
                                          Â
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > T1T2Calculate 
            Difrrence time
            >
            >
            >
            > 07:30
            > 08:10
            >
            >
            >
            >
            >
            > 07:30
            > 08:00
            >
            >
            >
            >
            >
            > 07:30
            > 08:08
            >
            >
            >
            >
            > Total
            > ?
            > ?
            >
            >
            >
            >
            >
            >
            >
            >
            > Regards
            >
            >
            >
            >
            >
            >
            >
            > Zahid
            Waseem
            >

          Your message has been successfully submitted and would be delivered to recipients shortly.