This discussion is archived
3 Replies Latest reply: Apr 19, 2013 4:22 AM by FrançoisDegrelle RSS

stored the time in oracle form 10g

khan786 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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çoisDegrelle Oracle ACE
    Currently Being Moderated
    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çoisDegrelle Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points