Return a date (This is a calculated date based on the input arguments) It need to Take 3 arguments
Starting Date (Date) (Example 10/10/2005)
Increment (integer) (Example è +4 or -3)
Day of the Week (Varchar)
(Example Monday or Thursday)


Example: If I need to calculate the 5th Monday after 10/10/2005 I will call the function like this è

Exec fnCalculateNewDate @startDate= ‘10/10/2005’, @Increment=+5, @DayOfTheWeek=’Monday’

If I need to calculate 4th Sunday before ‘10/10/2005, I will call the function line this

Exec fnCalculateNewDate @startDate= ‘10/10/2005’, @Increment=-4, @DayOfTheWeek=’Sunday’

Hi,
I have written the function. Just compile it & run with the proper parameters. Please make appropiate exception handling & format in the function.d_Date format should be appropiate according to ur NLS_DATE_FORMAT settings. U can use also TO_CHAR formatting in the function....

================================================== =========
CREATE OR REPLACE FUNCTION DateExample(d_Date IN DATE,
                                       i_Val IN NUMBER,
                                       v_day IN VARCHAR2) RETURN DATE AS
  sSqlQuery VARCHAR2(100);
  d_computedDate DATE;
BEGIN
  IF i_Val > 0 THEN
    sSqlQuery := 'SELECT NEXT_DAY(''' || d_Date || ''',''' || UPPER(v_day) ||
                 ''') + ' || i_val * 7 || ' FROM DUAL';
  ELSE
    sSqlQuery := 'SELECT NEXT_DAY(''' || d_Date || ''',''' || UPPER(v_day) ||
                 ''') - ' || ABS(i_Val) * 7 || ' FROM DUAL';
  END IF;
  EXECUTE IMMEDIATE (sSqlQuery)
    INTO d_computedDate;
  RETURN d_computedDate;
END DateExample;
================================================== =========

Subhendu De
[email protected]
Software Developer
New Delhi, India

Thanks a lot it works!!! As they say in Star Wars "May the force be with you"