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

RE: [PkOug] Re: Calculate difference in time and convert to numeric format

Expand Messages
  • Imran Arshad
    I think this will solve the problem. select round(to_date( 02-Jan-2006 02:20:28 , DD-Mon-RRRR HH24:MI:SS ) - to_date( 01-Jan-2006 01:10:14 , DD-Mon-RRRR
    Message 1 of 14 , Feb 28, 2006
    • 0 Attachment
      I think this will solve the problem.

      select round(to_date('02-Jan-2006 02:20:28','DD-Mon-RRRR HH24:MI:SS') -
      to_date('01-Jan-2006 01:10:14','DD-Mon-RRRR HH24:MI:SS')) Days,

      mod(trunc((to_date('02-Jan-2006 02:20:28','DD-Mon-RRRR
      HH24:MI:SS') -
      to_date('01-Jan-2006 01:10:14','DD-Mon-RRRR HH24:MI:SS'))*24),24)
      ||':'||

      mod(trunc((to_date('02-Jan-2006 02:20:28','DD-Mon-RRRR
      HH24:MI:SS') -
      to_date('01-Jan-2006 01:10:14','DD-Mon-RRRR
      HH24:MI:SS'))*24*60),60) ||':'||

      mod(trunc((to_date('02-Jan-2006 02:20:28','DD-Mon-RRRR
      HH24:MI:SS') -
      to_date('01-Jan-2006 01:10:14','DD-Mon-RRRR
      HH24:MI:SS'))*24*60*60),60) Difference

      from dual

      Thanks & Regards
      Muhammad Imran Arshad

      -----Original Message-----
      From: PkOug@yahoogroups.com [mailto:PkOug@yahoogroups.com] On Behalf Of
      Farooq Mobashir Hussain
      Sent: Wednesday, March 01, 2006 10:20 AM
      To: PkOug@yahoogroups.com
      Subject: [PkOug] Re: Calculate difference in time and convert to numeric
      format

      AOA

      Thanks everyone for your help, however I am still facing some
      problems ...


      Imran Arshad's query :

      select
      to_char(TRUNC(SYSDATE)+(to_date('11:55','hh24:mi') -
      to_date('10:15','hh24:mi')),'HH24:MI') "Elapsed Time"
      from dual

      did give a result however i am getting something like

      START_TIME END_TIME DIFF DIFF_IN_HRS
      ----------- --------- -------- ----------
      09:35:00 09:45:00 23:10:00 23.166

      I am looking for a way to subtract another 23 hours from the result

      Rehan Mirza's query :

      SELECT TO_CHAR(
      TO_DATE(
      ROUND(
      TO_NUMBER
      (END_TIME - START_TIME)*86400),'SSSSS'),'HH24:MI:SS')
      FROM NEW_AVAIL

      does not work since my datatypes for the two columns are VARCHAR2

      I still have to try out Aamir Cheema's query.

      Regards,

      Farooq





      --- In PkOug@yahoogroups.com, "Rehan Mirza" <mirza_rehan@...> wrote:
      >
      > Assalam-o-Alaikum
      >
      > This is Rehan Mirza from Karachi, Pakistan
      > I assume that both column has date data type
      >
      > SELECT TO_CHAR(
      > TO_DATE(
      > ROUND(
      > TO_NUMBER
      > (EndTime - StTime)*86400),'SSSSS'),'HH24:MI:SS')
      > FROM YOURSTABLENAME
      >
      > Hope this query solve yours problem, if your have any query
      regarding
      > this or any other problem plase mail me.
      >
      > Thanks and regards
      > Rehan Mirza
      > System Analyst / Software Developer
      > OCP Developer Rel. 2 / 6i
      >
      > --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
      > <farooq.mobashir@> wrote:
      > >
      > > Hello Gurus,
      > >
      > > My query .. I have two columns in a table in which one column has
      the
      > > start time of the database and the second column has the shutdown
      time
      > > of the database. Now in a new column I want the DIFFERENCE of
      these two
      > > times. Also in yet another column I want the numeric value of the
      time
      > > difference.
      > >
      > > for example :
      > >
      > > Start time Shut time Difference
      Diff_in_Num_Hrs
      > > ---------- ------------- ------------- -----------
      ---
      > > 09:35:09 09:45:09 00:10:00 0.166
      > >
      > > Any help will be highly appreciated.
      > >
      > > Regards,
      > >
      > > Farooq.
      > >
      >









      Offical Website http://www.pkoug.org
      Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux, CG,
      Jobs)
      dba@...
      ids@...
      ias@...
      java@...
      ocs@...
      apps@...
      linux@...
      cg@...
      jobs@...

      Pakistan Oracle Users Group
      Yahoo! Groups Links
    • Rehan Mirza
      Assalam-o-Alaikum use this one and tell me about it. SELECT TO_CHAR( TO_DATE( ROUND( TO_NUMBER (to_date(END_TIME, HH24:MI:SS ) -
      Message 2 of 14 , Feb 28, 2006
      • 0 Attachment
        Assalam-o-Alaikum

        use this one and tell me about it.

        SELECT TO_CHAR(
        TO_DATE(
        ROUND(
        TO_NUMBER
        (to_date(END_TIME,'HH24:MI:SS') -
        to_Date(START_TIME,'HH24:MI:SS'))*86400),'SSSSS'),'HH24:MI:SS')
        FROM NEW_AVAIL;

        but please tell if u dont save date with time how u calculate
        difference for example

        START_TIME END_TIME DIFF_IN_HRS
        ------------------- ------------------- ----------
        28-02-2006 23:05:00 01-03-2006 02:05:00 03:00:00

        how its possible?????

        START_TIME END_TIME DIFF DIFF_IN_HRS
        ----------- --------- -------- ----------
        09:35:00 09:45:00 23:10:00 23.166

        u also give me wrong answer for this i think its answer is

        START_TIME END_TIME DIFF DIFF_IN_HRS
        ----------- --------- -------- ----------
        09:35:00 09:45:00 00:10:00 .166

        OR

        START_TIME END_TIME DIFF DIFF_IN_HRS
        ----------- --------- -------- ----------
        09:35:00 09:45:00 24:10:00 24.166

        please tell me if i am wrong


        Thanks and regards
        Rehan Mirza
        System Analyst / Software Developer
        OCP Developer Rel. 2 / 6i


        --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
        <farooq.mobashir@...> wrote:
        >
        > AOA
        >
        > Thanks everyone for your help, however I am still facing some
        > problems ...
        >
        >
        > Imran Arshad's query :
        >
        > select
        > to_char(TRUNC(SYSDATE)+(to_date('11:55','hh24:mi') -
        > to_date('10:15','hh24:mi')),'HH24:MI') "Elapsed Time"
        > from dual
        >
        > did give a result however i am getting something like
        >
        > START_TIME END_TIME DIFF DIFF_IN_HRS
        > ----------- --------- -------- ----------
        > 09:35:00 09:45:00 23:10:00 23.166
        >
        > I am looking for a way to subtract another 23 hours from the result
        >
        > Rehan Mirza's query :
        >
        > SELECT TO_CHAR(
        > TO_DATE(
        > ROUND(
        > TO_NUMBER
        > (END_TIME - START_TIME)*86400),'SSSSS'),'HH24:MI:SS')
        > FROM NEW_AVAIL
        >
        > does not work since my datatypes for the two columns are VARCHAR2
        >
        > I still have to try out Aamir Cheema's query.
        >
        > Regards,
        >
        > Farooq
        >
        >
        >
        >
        >
        > --- In PkOug@yahoogroups.com, "Rehan Mirza" <mirza_rehan@> wrote:
        > >
        > > Assalam-o-Alaikum
        > >
        > > This is Rehan Mirza from Karachi, Pakistan
        > > I assume that both column has date data type
        > >
        > > SELECT TO_CHAR(
        > > TO_DATE(
        > > ROUND(
        > > TO_NUMBER
        > > (EndTime - StTime)*86400),'SSSSS'),'HH24:MI:SS')
        > > FROM YOURSTABLENAME
        > >
        > > Hope this query solve yours problem, if your have any query
        > regarding
        > > this or any other problem plase mail me.
        > >
        > > Thanks and regards
        > > Rehan Mirza
        > > System Analyst / Software Developer
        > > OCP Developer Rel. 2 / 6i
        > >
        > > --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
        > > <farooq.mobashir@> wrote:
        > > >
        > > > Hello Gurus,
        > > >
        > > > My query .. I have two columns in a table in which one column has
        > the
        > > > start time of the database and the second column has the shutdown
        > time
        > > > of the database. Now in a new column I want the DIFFERENCE of
        > these two
        > > > times. Also in yet another column I want the numeric value of the
        > time
        > > > difference.
        > > >
        > > > for example :
        > > >
        > > > Start time Shut time Difference
        > Diff_in_Num_Hrs
        > > > ---------- ------------- ------------- -----------
        > ---
        > > > 09:35:09 09:45:09 00:10:00 0.166
        > > >
        > > > Any help will be highly appreciated.
        > > >
        > > > Regards,
        > > >
        > > > Farooq.
        > > >
        > >
        >
      • Sikandar Hayat Awan
        Hi, http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=2363 Hope the link would be of help. Thanks ... -- Assalamu Alaikum! Thanks. Regards,
        Message 3 of 14 , Mar 1, 2006
        • 0 Attachment
          Hi,
          http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=2363

          Hope the link would be of help.

          Thanks

          On 3/1/06, Rehan Mirza <mirza_rehan@...> wrote:
          Assalam-o-Alaikum

          Yes you are absolutly right Mr. Qaisar-ul-Hassan

          Thanks and regards
          Rehan Mirza
          System Analyst / Software Developer
          OCP Developer Rel. 2 / 6i

          --- In PkOug@yahoogroups.com, quhassan@... wrote:
          >
          >
          > Its a good query to calculate difference within 24 hours,
          >
          > Query will fail if you want to know the time difference between i.e..
          > 25-FEB-2006 12:30:00 and 28-FEB-2006 17:18:12
          >
          > Other wise to calculate the time difference within same date, its a good
          > way to know the difference.
          >
          > Qaisar-ul-Hassan
          >
          > Engro Chemical Pakistan Limited
          >
          >
          >
          >

          >                       "Rehan Mirza"

          >                       <mirza_rehan@yaho        To:
          PkOug@yahoogroups.com
          >                       o.com>                   cc:

          >                       Sent by:                 Subject:  [PkOug] Re:
          Calculate difference in time and
          >                       PkOug@yahoogroups          convert to numeric
          format
          >                       .com

          >

          >

          >                       03/01/2006 09:15

          >                       AM

          >                       Please respond to

          >                       PkOug

          >

          >

          >
          >
          >
          > Assalam-o-Alaikum
          >
          > This is Rehan Mirza from Karachi, Pakistan
          > I assume that both column has date data type
          >
          > SELECT TO_CHAR(
          >         TO_DATE(
          >         ROUND(
          >         TO_NUMBER
          >   (EndTime - StTime)*86400),'SSSSS'),'HH24:MI:SS')
          > FROM YOURSTABLENAME
          >
          > Hope this query solve yours problem, if your have any query regarding
          > this or any other problem plase mail me.
          >
          > Thanks and regards
          > Rehan Mirza
          > System Analyst / Software Developer
          > OCP Developer Rel. 2 / 6i
          >
          > --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
          > <farooq.mobashir@> wrote:
          > >
          > > Hello Gurus,
          > >
          > > My query .. I have two columns in a table in which one column has the
          > > start time of the database and the second column has the shutdown time
          > > of the database. Now in a new column I want the DIFFERENCE of
          these two
          > > times. Also in yet another column I want the numeric value of the time
          > > difference.
          > >
          > > for example :
          > >
          > > Start time       Shut time         Difference          Diff_in_Num_Hrs
          > > ----------       -------------     -------------       --------------
          > > 09:35:09          09:45:09          00:10:00            0.166
          > >
          > > Any help will be highly appreciated.
          > >
          > > Regards,
          > >
          > > Farooq.
          > >
          >
          >
          >
          >
          >
          >
          > Offical Website http://www.pkoug.org
          > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux, CG,
          Jobs)
          >    dba@...
          >    ids@...
          >    ias@...
          >    java@...
          >    ocs@...
          >    apps@...
          >    linux@...
          >    cg@...
          >    jobs@...
          >
          > Pakistan Oracle Users Group
          > Yahoo! Groups Links
          >







          Offical Website http://www.pkoug.org
          Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux, CG, Jobs)
             dba@...
             ids@...
             ias@...
             java@...
             ocs@...
             apps@...
             linux@...
             cg@...
             jobs@...

          Pakistan Oracle Users Group
          Yahoo! Groups Links

          <*> To visit your group on the web, go to:
              http://groups.yahoo.com/group/PkOug/

          <*> To unsubscribe from this group, send an email to:
              PkOug-unsubscribe@yahoogroups.com

          <*> Your use of Yahoo! Groups is subject to:
              http://docs.yahoo.com/info/terms/






          --
          Assalamu Alaikum!



          Thanks.

          Regards,
          Sikandar Hayat
        • Farooq Mobashir Hussain
          AOA Rehan your query worked beautifully !! Thank you so much ! Also thanks to all the other professionals who helped me out. Let s keep the discussions going !
          Message 4 of 14 , Mar 1, 2006
          • 0 Attachment
            AOA

            Rehan your query worked beautifully !! Thank you so much !

            Also thanks to all the other professionals who helped me out. Let's
            keep the discussions going !

            Kind Regards,

            Farooq.



            --- In PkOug@yahoogroups.com, "Rehan Mirza" <mirza_rehan@...> wrote:
            >
            > Assalam-o-Alaikum
            >
            > Yes you are absolutly right Mr. Qaisar-ul-Hassan
            >
            > Thanks and regards
            > Rehan Mirza
            > System Analyst / Software Developer
            > OCP Developer Rel. 2 / 6i
            >
            > --- In PkOug@yahoogroups.com, quhassan@ wrote:
            > >
            > >
            > > Its a good query to calculate difference within 24 hours,
            > >
            > > Query will fail if you want to know the time difference between
            i.e..
            > > 25-FEB-2006 12:30:00 and 28-FEB-2006 17:18:12
            > >
            > > Other wise to calculate the time difference within same date, its
            a good
            > > way to know the difference.
            > >
            > > Qaisar-ul-Hassan
            > >
            > > Engro Chemical Pakistan Limited
            > >
            > >
            > >
            >
            >
            >
            > > "Rehan
            Mirza"
            >
            > > <mirza_rehan@yaho To:
            > PkOug@yahoogroups.com
            > > o.com>
            cc:
            >
            > > Sent by: Subject: [PkOug]
            Re:
            > Calculate difference in time and
            > > PkOug@yahoogroups convert to
            numeric
            > format
            >
            > .com
            >
            >
            >
            >
            >
            >
            >
            > > 03/01/2006
            09:15
            >
            > >
            AM
            >
            > > Please respond
            to
            >
            > >
            PkOug
            >
            >
            >
            >
            >
            >
            >
            > >
            > >
            > >
            > > Assalam-o-Alaikum
            > >
            > > This is Rehan Mirza from Karachi, Pakistan
            > > I assume that both column has date data type
            > >
            > > SELECT TO_CHAR(
            > > TO_DATE(
            > > ROUND(
            > > TO_NUMBER
            > > (EndTime - StTime)*86400),'SSSSS'),'HH24:MI:SS')
            > > FROM YOURSTABLENAME
            > >
            > > Hope this query solve yours problem, if your have any query
            regarding
            > > this or any other problem plase mail me.
            > >
            > > Thanks and regards
            > > Rehan Mirza
            > > System Analyst / Software Developer
            > > OCP Developer Rel. 2 / 6i
            > >
            > > --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
            > > <farooq.mobashir@> wrote:
            > > >
            > > > Hello Gurus,
            > > >
            > > > My query .. I have two columns in a table in which one column
            has the
            > > > start time of the database and the second column has the
            shutdown time
            > > > of the database. Now in a new column I want the DIFFERENCE of
            > these two
            > > > times. Also in yet another column I want the numeric value of
            the time
            > > > difference.
            > > >
            > > > for example :
            > > >
            > > > Start time Shut time Difference
            Diff_in_Num_Hrs
            > > > ---------- ------------- ------------- ---------
            -----
            > > > 09:35:09 09:45:09 00:10:00 0.166
            > > >
            > > > Any help will be highly appreciated.
            > > >
            > > > Regards,
            > > >
            > > > Farooq.
            > > >
            > >
            > >
            > >
            > >
            > >
            > >
            > > Offical Website http://www.pkoug.org
            > > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux,
            CG,
            > Jobs)
            > > dba@
            > > ids@
            > > ias@
            > > java@
            > > ocs@
            > > apps@
            > > linux@
            > > cg@
            > > jobs@
            > >
            > > Pakistan Oracle Users Group
            > > Yahoo! Groups Links
            > >
            >
          • Muhammad Shuja
            It s good that you found the query you were looking for. I would also suggest to save the time (as date datatype), directly in start/end time columns, using
            Message 5 of 14 , Mar 1, 2006
            • 0 Attachment
              It's good that you found the query you were looking for.
               
              I would also suggest to save the time (as date datatype), directly in start/end time columns, using database startup/shutdown triggers. Then you can simply substract the two columns to get time (divide days to hour and minutes appropriately). It will handle everything, am/pm or multiple days or hours etc.
               
              regards
              Shuja

               
              On 3/1/06, Farooq Mobashir Hussain <farooq.mobashir@...> wrote:
              AOA

              Rehan your query worked beautifully !! Thank you so much !

              Also thanks to all the other professionals who helped me out. Let's
              keep the discussions going !

              Kind Regards,

              Farooq.



              --- In PkOug@yahoogroups.com , "Rehan Mirza" <mirza_rehan@...> wrote:
              >
              > Assalam-o-Alaikum
              >
              > Yes you are absolutly right Mr. Qaisar-ul-Hassan
              >
              > Thanks and regards
              > Rehan Mirza
              > System Analyst / Software Developer
              > OCP Developer Rel. 2 / 6i
              >
              > --- In PkOug@yahoogroups.com, quhassan@ wrote:
              > >
              > >
              > > Its a good query to calculate difference within 24 hours,
              > >
              > > Query will fail if you want to know the time difference between
              i.e..
              > > 25-FEB-2006 12:30:00 and 28-FEB-2006 17:18:12
              > >
              > > Other wise to calculate the time difference within same date, its
              a good
              > > way to know the difference.
              > >
              > > Qaisar-ul-Hassan
              > >
              > > Engro Chemical Pakistan Limited
              > >
              > >
              > >
              >
              >                                                                   
              >                                        
              > >                       "Rehan
              Mirza"                               
              >                                        
              > >                       <mirza_rehan@yaho        To:     
              > PkOug@yahoogroups.com                             
              > >                       o.com>                  
              cc:                
              >                                        
              > >                       Sent by:                 Subject:  [PkOug]
              Re:
              > Calculate difference in time and      
              > >                       PkOug@yahoogroups         convert to
              numeric
              >  format                                
              >
              >                       .com                                        
              >                                        
              >
              >                                                                   
              >                                        
              >
              >                                                                   
              >                                        
              > >                       03/01/2006
              09:15                            
              >                                        
              > >                      
              AM                                          
              >                                        
              > >                       Please respond
              to                           
              >                                        
              > >                      
              PkOug                                       
              >                                        
              >
              >                                                                   
              >                                        
              >
              >                                                                   
              >                                        
              > >
              > >
              > >
              > > Assalam-o-Alaikum
              > >
              > > This is Rehan Mirza from Karachi, Pakistan
              > > I assume that both column has date data type
              > >
              > > SELECT TO_CHAR(
              > >         TO_DATE(
              > >         ROUND(
              > >         TO_NUMBER
              > >   (EndTime - StTime)*86400),'SSSSS'),'HH24:MI:SS')
              > > FROM YOURSTABLENAME
              > >
              > > Hope this query solve yours problem, if your have any query
              regarding
              > > this or any other problem plase mail me.
              > >
              > > Thanks and regards
              > > Rehan Mirza
              > > System Analyst / Software Developer
              > > OCP Developer Rel. 2 / 6i
              > >
              > > --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
              > > <farooq.mobashir@> wrote:
              > > >
              > > > Hello Gurus,
              > > >
              > > > My query .. I have two columns in a table in which one column
              has the
              > > > start time of the database and the second column has the
              shutdown time
              > > > of the database. Now in a new column I want the DIFFERENCE of
              > these two
              > > > times. Also in yet another column I want the numeric value of
              the time
              > > > difference.
              > > >
              > > > for example :
              > > >
              > > > Start time       Shut time         Difference         
              Diff_in_Num_Hrs
              > > > ----------       -------------     -------------       ---------
              -----
              > > > 09:35:09          09:45:09          00:10:00            0.166
              > > >
              > > > Any help will be highly appreciated.
              > > >
              > > > Regards,
              > > >
              > > > Farooq.
              > > >
              > >
              > >
              > >
              > >
              > >
              > >
              > > Offical Website http://www.pkoug.org
              > > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux,
              CG,
              > Jobs)
              > >    dba@
              > >    ids@
              > >    ias@
              > >    java@
              > >    ocs@
              > >    apps@
              > >    linux@
              > >    cg@
              > >    jobs@
              > >
              > > Pakistan Oracle Users Group
              > > Yahoo! Groups Links
              > >
              >






              Offical Website http://www.pkoug.org
              Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux, CG, Jobs)
                 dba@...
                 ids@...
                 ias@...
                 java@...
                 ocs@...
                 apps@...
                 linux@...
                 cg@...
                 jobs@...

              Pakistan Oracle Users Group




              YAHOO! GROUPS LINKS




            • tariq-saeed@att.net
              Assalaamu Alaikum, Farooq, most probably you are storing date/time after converting into characters. Oracle stores, uses and manipulates date-time as one
              Message 6 of 14 , Mar 1, 2006
              • 0 Attachment

                Assalaamu Alaikum,

                 

                Farooq, most probably you are storing date/time after converting into characters. Oracle stores, uses and manipulates date-time as one DATA-type. You can do all kind maths and "alphanumeric manipulations" with this "date-time" data type. The need to convert comes into the picture ONLY when you have to display/show it on the screen or print it in a report.


                If you store "start time" as well as "stop time" as "dates", you can easily find the difference, convert the difference into hours, minutes or whatever units you want without much effort.

                Hope it helps, insha'Allah.

                Wassalaam.

                __________

                Tariq Saeed

                Oracle Corporation,

                Chicago, USA.

                 

                -------------- Original message from "Rehan Mirza" <mirza_rehan@...>: --------------


                > Assalam-o-Alaikum
                >
                > use this one and tell me about it.
                >
                > SELECT TO_CHAR(
                > TO_DATE(
                > ROUND(
                > TO_NUMBER
                > (to_date(END_TIME,'HH24:MI:SS') -
                > to_Date(START_TIME,'HH24:MI:SS'))*86400),'SSSSS'),'HH24:MI:SS')
                > FROM NEW_AVAIL;
                >
                > but please tell if u dont save date with time how u calculate
                > difference for example
                >
                > START_TIME END_TIME DIFF_IN_HRS
                > ------------------- ------------------- ----------
                > 28-02-2006 23:05:00 01-03-2006 02:05:00 03:00:00
                >
                > how its possible?????
                >
                > START_TIME END_TIME DIFF DIFF_IN_HRS
                > ----------- --------- -------- ----------
                > 09:35:00 09:45:00 23:10:00 23.166
                >
                > u also give me wrong answer for this i think its answer is
                >
                > START_TIME END_TIME DIFF DIFF_IN_HRS
                > ----------- --------- -------- ----------
                > 09:35:00 09:45:00 00:10:00 .166
                >
                > OR
                >
                > START_TIME END_TIME DIFF DIFF_IN_HRS
                > ----------- --------- -------- ----------
                > 09:35:00 09:45:00 24:10:00 24.166
                >
                > please tell me if i am wrong
                >
                >
                > Thanks and regards
                > Rehan Mirza
                > System Analyst / Software Developer
                > OCP Developer Rel. 2 / 6i
                >
                >
                > --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
                > wrote:
                > >
                > > AOA
                > >
                > > Thanks everyone for your help, however I am still facing some
                > > problems ...
                > >
                > >
                > > Imran Arshad's query :
                > >
                > > select
                > > to_char(TRUNC(SYSDATE)+(to_date('11:55','hh24:mi') -
                > > to_date('10:15','hh24:mi')),'HH24:MI') "Elapsed Time"
                > > from dual
                > >
                > > did give a result however i am getting something like
                > >
                > > START_TIME END_TIME DIFF DIFF_IN_HRS
                > > ----------- --------- -------- ----------
                > > 09:35:00 09:45:00 23:10:00 23.166
                > >
                > > I am looking for a way to subtract another 23 hours from the result
                > >
                > > Rehan Mirza's query :
                > >
                > > SELECT TO_CHAR(
                > > TO_DATE(
                > > ROUND(
                > > TO_NUMBER
                > > (END_TIME - START_TIME)*86400),'SSSSS'),'HH24:MI:SS')
                > > FROM NEW_AVAIL
                > >
                > > does not work since my datatypes for the two columns are VARCHAR2
                > >
                > > I still have to try out Aamir Cheema's query.
                > >
                > > Regards,
                > >
                > > Farooq
                > >
                > >
                > >
                > >
                > >
                > > --- In PkOug@yahoogroups.com, "Rehan Mirza" wrote:
                > > >
                > > > Assalam-o-Alaikum
                > > >
                > > > This is Rehan Mirza from Karachi, Pakistan
                > > > I assume that both column has date data type
                > > >
                > > > SELECT TO_CHAR(
                > > > TO_DATE(
                > > > ROUND(
                > > > TO_NUMBER
                > > > (EndTime - StTime)*86400),'SSSSS'),'HH24:MI:SS')
                > > > FROM YOURSTABLENAME
                > > >
                > > > Hope this query solve yours problem, if your have any query
                > > regarding
                > > > this or any other problem plase mail me.
                > > >
                > > > Thanks and regards
                > > > Rehan Mirza
                > > > System Analyst / Software Developer
                > > > OCP Developer Rel. 2 / 6i
                > > >
                > > > --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
                > > > wrote:
                > > > >
                > > > > Hello Gurus,
                > > > >
                > > > > My query .. I have two columns in a table in which one column has
                > > the
                > > > > start time of the database and the second column has the shutdown
                > > time
                > > > > of the database. Now in a new column I want the DIFFERENCE of
                > > these two
                > > > > times. Also in yet another column I want the numeric value of the
                > > time
                > > > > difference.
                > > > >
                > > > > for example :
                > > > >
                > > > > Start time Shut time Difference
                > > Diff_in_Num_Hrs
                > > > > ---------- ------------- ------------- -----------
                > > ---
                > > > > 09:35:09 09:45:09 00:10:00 0.166
                > > > >
                > > > > Any help will be highly appreciated.
                > > > >
                > > > > Regards,
                > > > >
                > > > > Farooq.
                > > > >
                > > >
                > >
                >
                >
                >
                >
                >
                >
                >
                >
                > Offical Website http://www.pkoug.org
                > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux, CG, Jobs)
                > dba@...
                > ids@...
                > ias@...
                > java@...
                > ocs@...
                > apps@...
                > linux@...
                > cg@...
                > jobs@...
                >
                > Pakistan Oracle Users Group
                > Yahoo! Groups Links
                >
                > <*> To visit your group on the web, go to:
                > http://groups.yahoo.com/group/PkOug/
                >
                > <*> To unsubscribe from this group, send an email to:
                > PkOug-unsubscribe@yahoogroups.com
                >
                > <*> Your use of Yahoo! Groups is subject to:
                > http://docs.yahoo.com/info/terms/
                >
                >
                >
              • ZAHIDJEE
                Assalam o alaikum I need some material on changing various visual attributes of Forms... like to hide the title bar etc please let me know if there is some
                Message 7 of 14 , Mar 3, 2006
                • 0 Attachment
                  Assalam o alaikum

                  I need some material on changing various visual attributes of Forms... like
                  to hide the title bar etc
                  please let me know if there is some link for this


                  ----- Original Message -----
                  From: "Farooq Mobashir Hussain" <farooq.mobashir@...>
                  To: <PkOug@yahoogroups.com>
                  Sent: Wednesday, March 01, 2006 2:42 PM
                  Subject: [PkOug] Re: Calculate difference in time and convert to numeric
                  format


                  > AOA
                  >
                  > Rehan your query worked beautifully !! Thank you so much !
                  >
                  > Also thanks to all the other professionals who helped me out. Let's
                  > keep the discussions going !
                  >
                  > Kind Regards,
                  >
                  > Farooq.
                  >
                  >
                  >
                  > --- In PkOug@yahoogroups.com, "Rehan Mirza" <mirza_rehan@...> wrote:
                  >>
                  >> Assalam-o-Alaikum
                  >>
                  >> Yes you are absolutly right Mr. Qaisar-ul-Hassan
                  >>
                  >> Thanks and regards
                  >> Rehan Mirza
                  >> System Analyst / Software Developer
                  >> OCP Developer Rel. 2 / 6i
                  >>
                  >> --- In PkOug@yahoogroups.com, quhassan@ wrote:
                  >> >
                  >> >
                  >> > Its a good query to calculate difference within 24 hours,
                  >> >
                  >> > Query will fail if you want to know the time difference between
                  > i.e..
                  >> > 25-FEB-2006 12:30:00 and 28-FEB-2006 17:18:12
                  >> >
                  >> > Other wise to calculate the time difference within same date, its
                  > a good
                  >> > way to know the difference.
                  >> >
                  >> > Qaisar-ul-Hassan
                  >> >
                  >> > Engro Chemical Pakistan Limited
                  >> >
                  >> >
                  >> >
                  >>
                  >>
                  >>
                  >> > "Rehan
                  > Mirza"
                  >>
                  >> > <mirza_rehan@yaho To:
                  >> PkOug@yahoogroups.com
                  >> > o.com>
                  > cc:
                  >>
                  >> > Sent by: Subject: [PkOug]
                  > Re:
                  >> Calculate difference in time and
                  >> > PkOug@yahoogroups convert to
                  > numeric
                  >> format
                  >>
                  >> .com
                  >>
                  >>
                  >>
                  >>
                  >>
                  >>
                  >>
                  >> > 03/01/2006
                  > 09:15
                  >>
                  >> >
                  > AM
                  >>
                  >> > Please respond
                  > to
                  >>
                  >> >
                  > PkOug
                  >>
                  >>
                  >>
                  >>
                  >>
                  >>
                  >>
                  >> >
                  >> >
                  >> >
                  >> > Assalam-o-Alaikum
                  >> >
                  >> > This is Rehan Mirza from Karachi, Pakistan
                  >> > I assume that both column has date data type
                  >> >
                  >> > SELECT TO_CHAR(
                  >> > TO_DATE(
                  >> > ROUND(
                  >> > TO_NUMBER
                  >> > (EndTime - StTime)*86400),'SSSSS'),'HH24:MI:SS')
                  >> > FROM YOURSTABLENAME
                  >> >
                  >> > Hope this query solve yours problem, if your have any query
                  > regarding
                  >> > this or any other problem plase mail me.
                  >> >
                  >> > Thanks and regards
                  >> > Rehan Mirza
                  >> > System Analyst / Software Developer
                  >> > OCP Developer Rel. 2 / 6i
                  >> >
                  >> > --- In PkOug@yahoogroups.com, "Farooq Mobashir Hussain"
                  >> > <farooq.mobashir@> wrote:
                  >> > >
                  >> > > Hello Gurus,
                  >> > >
                  >> > > My query .. I have two columns in a table in which one column
                  > has the
                  >> > > start time of the database and the second column has the
                  > shutdown time
                  >> > > of the database. Now in a new column I want the DIFFERENCE of
                  >> these two
                  >> > > times. Also in yet another column I want the numeric value of
                  > the time
                  >> > > difference.
                  >> > >
                  >> > > for example :
                  >> > >
                  >> > > Start time Shut time Difference
                  > Diff_in_Num_Hrs
                  >> > > ---------- ------------- ------------- ---------
                  > -----
                  >> > > 09:35:09 09:45:09 00:10:00 0.166
                  >> > >
                  >> > > Any help will be highly appreciated.
                  >> > >
                  >> > > Regards,
                  >> > >
                  >> > > Farooq.
                  >> > >
                  >> >
                  >> >
                  >> >
                  >> >
                  >> >
                  >> >
                  >> > Offical Website http://www.pkoug.org
                  >> > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux,
                  > CG,
                  >> Jobs)
                  >> > dba@
                  >> > ids@
                  >> > ias@
                  >> > java@
                  >> > ocs@
                  >> > apps@
                  >> > linux@
                  >> > cg@
                  >> > jobs@
                  >> >
                  >> > Pakistan Oracle Users Group
                  >> > Yahoo! Groups Links
                  >> >
                  >>
                  >
                  >
                  >
                  >
                  >
                  >
                  > Offical Website http://www.pkoug.org
                  > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux, CG, Jobs)
                  > dba@...
                  > ids@...
                  > ias@...
                  > java@...
                  > ocs@...
                  > apps@...
                  > linux@...
                  > cg@...
                  > jobs@...
                  >
                  > Pakistan Oracle Users Group
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                Your message has been successfully submitted and would be delivered to recipients shortly.