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

Time Calculate Problem

Expand Messages
  • zahid
    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
    Message 1 of 7 , Feb 15, 2013
    • 0 Attachment

      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
    • Mirza Zeeshan Baig
      Dear, Below query will help you. select trunc(mod((to_date( 18-feb-2013 07:30 , dd-mon-yyyy hh24:mi ) - to_date( 18-feb-2013 07:00 , dd-mon-yyyy hh24:mi )) *
      Message 2 of 7 , Feb 17, 2013
      • 0 Attachment
        Dear,
        Below query will help you.

        select trunc(mod((to_date('18-feb-2013 07:30', 'dd-mon-yyyy hh24:mi') -
        to_date('18-feb-2013 07:00', 'dd-mon-yyyy hh24:mi')) * 24,
        24)) || ':' ||
        trunc(mod((to_date('18-feb-2013 07:30', 'dd-mon-yyyy hh24:mi') -
        to_date('18-feb-2013 07:00', 'dd-mon-yyyy hh24:mi')) * 24 * 60,
        60))
        from dual;


        Regards,
        Mirza Zeeshan Baig


        --- In PkOug@yahoogroups.com, zahid <zaidi_005@...> wrote:
        >
        > 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);  
        >                                      
        >
        >
        >
        >
        >
        >
        >
        >
        > T1T2
        > Calculate  Difrrence time
        >
        >
        >
        > 07:30
        > 08:10
        >
        >
        >
        >
        >
        > 07:30
        > 08:00
        >
        >
        >
        >
        >
        > 07:30
        > 08:08
        >
        >
        >
        >
        > Total
        > ?
        > ?
        >
        >
        >
        >
        >
        >
        >
        >
        > Regards
        >
        >
        >
        >
        >
        >
        >
        > Zahid Waseem
        >
      • abid hussain
        W salam You first have convert hours into minute, then calculate total minutes Fallowing example will help you Suupose you have a table test12 having a column
        Message 3 of 7 , Feb 17, 2013
        • 0 Attachment
          W'salam
           You first have convert hours into minute, then calculate total minutes

          Fallowing example will help you
          Suupose you have a table test12 having a column of time

          select * from test12

          10:29

          10:15

          10:16

           


          Now consider below query


          select trunc(total_minutes/60) as the_hour,
                 mod (total_minutes ,
          60) as the_minute

          from
          (
             select Sum(To_Number(substr(hour,
          1,2)))*60 +Sum(To_Number(substr(hour,4,5))) total_minutes
              from   test12
          )

           

          output

           

          THE_HOUR    THE_MINUTE

          31            00




          Abid


          On Sat, Feb 16, 2013 at 11:14 AM, zahid <zaidi_005@...> wrote:
           

          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);  


                                               

          T1T2 Calculate  Difrrence time
          07:3008:10
          07:3008:00
          07:3008:08
          Total??



          Regards



          Zahid Waseem




          --
          Regards

          ABID HUSSAIN

          Oracle Technical Consultant
          Sapphire Consulting Services
        • 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 4 of 7 , Feb 18, 2013
          • 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 5 of 7 , Feb 19, 2013
            • 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 6 of 7 , Feb 22, 2013
              • 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 7 of 7 , Feb 26, 2013
                • 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.