Mega Code Archive
Date time calculation function
<![CDATA[
SQL>
SQL> CREATE OR REPLACE FUNCTION expiration_date (
2 good_for_period_in IN INTERVAL YEAR TO MONTH
3 )
4 RETURN TIMESTAMP
5 IS
6 bad_month EXCEPTION;
7 PRAGMA EXCEPTION_INIT (bad_month, -1839);
8
9 todays_date TIMESTAMP;
10 result_date TIMESTAMP;
11 BEGIN
12 todays_date := TRUNC(SYSTIMESTAMP);
13 LOOP
14 BEGIN
15 result_date := todays_date + good_for_period_in;
16 EXCEPTION
17 WHEN bad_month THEN
18 todays_date := todays_date - INTERVAL '1' DAY;
19 WHEN OTHERS THEN
20 RAISE;
21 END;
22
23 EXIT WHEN result_date IS NOT NULL;
24 END LOOP;
25 RETURN result_date;
26 END;
27 /
Function created.
]]>