Dates in Oracle functions...

[Rizvan Asgarov]

Hi Friends,
I want to get number of days, hours and minutes between dates...

But, my script does not yield results true:

/* Formatted on 2009/12/10 09:07 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION time_diff (
   date_1   IN   DATE,
   date_2   IN   DATE
)
   RETURN VARCHAR2
IS
   nday_1              NUMBER (2, 0);
   nday_2              NUMBER (2, 0);
   nhour_1             NUMBER (4, 0);
   nhour_2             NUMBER (4, 0);
   nminute_1           NUMBER (2, 0);
   nminute_2           NUMBER (2, 0);
   difference          VARCHAR2 (200);
   day_difference      NUMBER;
   hour_difference     NUMBER;
   minute_difference   NUMBER;
   vday                VARCHAR2 (20);
   vhour               VARCHAR2 (20);
   vminute             VARCHAR2 (20);
   day_value           VARCHAR2 (200);
   hour_value          VARCHAR2 (200);
   minute_value        VARCHAR2 (200);
BEGIN
   vday := ' day ';
   vhour := ' hour ';
   vminute := ' minute ';
   nday_1 := TO_NUMBER (TO_CHAR (date_1, 'DD'));
   nday_2 := TO_NUMBER (TO_CHAR (date_2, 'DD'));
   nhour_1 := TO_NUMBER (TO_CHAR (date_1, 'HH24'));
   nhour_2 := TO_NUMBER (TO_CHAR (date_2, 'HH24'));
   nminute_1 := TO_NUMBER (TO_CHAR (date_1, 'MI'));
   nminute_2 := TO_NUMBER (TO_CHAR (date_2, 'MI'));
   day_difference := nday_2 - nday_1;
   hour_difference := nhour_2 - nhour_1;
   minute_difference := nminute_2 - nminute_1;

   IF hour_difference < 0
   THEN
      hour_value := hour_difference + 24 || vhour;
   ELSIF hour_difference = 0
   THEN
      hour_value := NULL;
   ELSE
      hour_value := hour_difference || vhour;
   END IF;

   IF day_difference = 0
   THEN
      day_value := NULL;
   ELSE
      day_value := day_difference || vday;
   END IF;

   IF minute_difference < 0
   THEN
      minute_value := hour_difference + 24 || vminute;
   ELSIF minute_difference = 0
   THEN
      minute_value := NULL;
   ELSE
      minute_value := minute_difference || vminute;
   END IF;

   IF minute_difference < 0
   THEN
      minute_difference := minute_difference + 60;
   END IF;

   difference := day_value || hour_value || minute_value;
   RETURN difference;
END time_diff;
/

Any help would be greatly appreciated,
Rizvan

[Christophe Verré]

Look at this article, "Subtraction of two TIMESTAMP datatypes"

[Rizvan Asgarov]

Thanks Christophe,

Yeah, I solved it by this article...

But, how do I use my select by function together?

SELECT TO_CHAR (:date_1, 'DD.MM.YYYY:HH24:MI:SS') "date_1",
       TO_CHAR (:date_2, 'DD.MM.YYYY:HH24:MI:SS') "date_2",
         TRUNC ((86400 * (:date_2 - :date_1)) / 60)
       - 60 * (TRUNC (((86400 * (:date_2 - :date_1)) / 60) / 60)) "Min",
         TRUNC (((86400 * (:date_2 - :date_1)) / 60) / 60)
       - 24 * (TRUNC ((((86400 * (:date_2 - :date_1)) / 60) / 60) / 24))
                                                                        "Hrs",
       TRUNC ((((86400 * (:date_2 - :date_1)) / 60) / 60) / 24) "Days"
  FROM DUAL;

Thanks in advance,
Rizvan

[Christophe Verré]

Inject the result of the SELECT into a variable, using "INTO difference".

[Rizvan Asgarov]

But, what is the mistake at the following code when using into clause:

/* Formatted on 2009/12/10 11:12 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION fhn_dictionaries.time_diff (
   date_1   IN   DATE,
   date_2   IN   DATE
)
   RETURN VARCHAR2
IS

    vday      VARCHAR2 (20);
    vhour     VARCHAR2 (20);
    vminute   VARCHAR2 (20);
    vdifference varchar(200);
    
 BEGIN   

SELECT TO_CHAR (:date_1, 'DD.MM.YYYY:HH24:MI:SS') ":date_1",
       TO_CHAR (:date_2, 'DD.MM.YYYY:HH24:MI:SS') ":date_2",
         TRUNC ((86400 * (:date_2 - :date_1)) / 60) - 60 * (TRUNC (((86400 * (:date_2 - :date_1)) / 60) / 60)) "Min",
         TRUNC (((86400 * (:date_2 - :date_1)) / 60) / 60) - 24 * (TRUNC ((((86400 * (:date_2 - :date_1)) / 60) / 60) / 24)) "Hrs",
         TRUNC ((((86400 * (:date_2 - :date_1)) / 60) / 60) / 24) "Days"
       into  vday, vhour, vminute                                                                     
  FROM dual;
  
  vdifference := vday || vhour || vminute;
  
  return vdifference;
  
  end;

Thanks in advance,