Calculate difference in date at runtime in oracle forms

Hi

If the columns are date-columns, u can subtract them. The result is the difference in days. If you want the difference in hours, minutes
Pls Try the following...


BEGIN

SELECT floor((:end_date - :start_date )*24)
|| ' HOURS ' ||
mod(floor((:end_date - :start_date )*24*60),60)
|| ' MINUTES ' ||
mod(floor(( :end_date - :start_date)*24*60*60),60)
|| ' SECS ' time_difference


INTO :total_time_difference

FROM table_dates

WHERE ..... ;

EXCEPTION

WHEN OTHERS THEN

Message ('OTHERS' || SQLCODE || ' And ' || SQLERRM);

END;


------------------------

Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.

Let's investigate some solutions. Test data:

SQL> CREATE TABLE dates (date1 DATE, date2 DATE);

Table created.

SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);

1 row created.

SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);

1 row created.

SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);

1 row created.

SQL> SELECT (date1 - date2) FROM dates;

DATE1-DATE2
-----------
1
.041666667
.000694444
Solution 1

SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
FROM dates;

TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS
Solution 2

If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):

SQL> SELECT to_number( to_char(to_date('1','J') +
(date1 - date2), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time
FROM dates;

DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00


--Gopi Golla