3 Replies Latest reply: Apr 19, 2013 6:22 AM by François Degrelle RSS

    stored the time in oracle form 10g

    khan786
      i have two field one is start_time and end_time to enter the task start_time and end_time.
      Now whats is datatype is should keep in oracle form and what at the database feild.
      i want enter 09:30 start_time and 12:30 to end_time and in third field the difference of these two fields.
        • 1. Re: stored the time in oracle form 10g
          tony.g
          Hi

          What data types have you tried so far?

          There is a datetime datatype, but date would work as well.

          What units did you want to store the diference between the two times? Minutes, hours, decimal days?

          rgds
          Tony
          • 2. Re: stored the time in oracle form 10g
            François Degrelle
            Hello,

            There is no standard function that gives the (comprehensive) difference between to hours (exept a result that is a day fraction), so you have to write your own.

            Francois
            • 3. Re: stored the time in oracle form 10g
              François Degrelle
              Here is a function that returnn the difference between to dates
              CREATE OR REPLACE FUNCTION Diff_Time
                (
                   LD$Start_date  IN DATE DEFAULT SYSDATE
                   ,LD$End_date    IN DATE DEFAULT SYSDATE
                   ,LN$JJ          OUT PLS_INTEGER
                   ,LN$HH          OUT PLS_INTEGER
                   ,LN$MI          OUT PLS_INTEGER
                   ,LN$SS          OUT PLS_INTEGER
                ) Return NUMBER
              IS
                dif   NUMBER ;
              Begin
                
                If LD$End_date < LD$Start_date Then
                   Return ( -1 ) ;
                End if ;
              
                Select  LD$End_date - LD$Start_date Into dif  From DUAL ;
                Select  trunc ( LD$End_date - LD$Start_date)  Into LN$JJ  From DUAL ;
                Select  trunc ( (LD$End_date - LD$Start_date) * 24) -  ( LN$JJ * 24 ) Into LN$HH From DUAL ;
                Select  trunc ( (LD$End_date - LD$Start_date) * 1440) - ( (LN$HH * 60) + ( LN$JJ * 1440) ) Into LN$MI From DUAL ;
                Select  trunc ( (LD$End_date - LD$Start_date) * 86400) - ( (LN$MI * 60) + (LN$HH * 3600) + ( LN$JJ * 3600 * 24 ) ) Into LN$SS From DUAL ;
              
                Return( dif ) ;
                
              End ;
              /
              Francois