Your own month add method : Utility Function « Stored Procedure Function « Oracle PL / SQL






Your own month add method

  
SQL>
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE FUNCTION add_mon (date_in DATE,months_to_add NUMBER)
  2  RETURN DATE AS
  3    day_in VARCHAR2(3);
  4    day_work VARCHAR2(3);
  5    date_out DATE;
  6    date_work DATE;
  7  BEGIN
  8
  9    day_in := TO_NUMBER(TO_CHAR(date_in,'dd'));
 10    date_work := ADD_MONTHS(date_in, months_to_add);
 11    day_work := TO_NUMBER(TO_CHAR(date_work,'dd'));
 12    IF day_in = day_work THEN
 13      date_out := date_work;
 14    ELSIF day_work < day_in THEN
 15      date_out := date_work;
 16    ELSE
 17      date_out := date_work - (day_work - day_in);
 18    END IF;
 19
 20    RETURN date_out;
 21  END;
 22  /

Function created.

SQL> show error
No errors.
SQL>
SQL> select add_mon(sysdate, 6) from dual;

ADD_MON(SYSDATE,6)
--------------------
16-DEC-2008 18:11:35

1 row selected.

SQL>
SQL> --

   
  








Related examples in the same category

1.Raise power function
2.Raise pay level
3.Validate date value format
4.Function to update null value
5.Function to count tab
6.Calculate average grade
7.Temperature package: convert Celsius to Fahrenheit back, forth