|
Subject:
|
SQL date Funcition
|
|
Posted By:
|
willpower
|
Post Date:
|
12/21/2005 12:50:46 PM
|
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’
|
|
Reply By:
|
subhendude
|
Reply Date:
|
12/25/2005 5:50:22 AM
|
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
subhendude@gmail.com
Software Developer
New Delhi, India
|
|
Reply By:
|
willpower
|
Reply Date:
|
1/4/2006 4:29:22 PM
|
Thanks a lot it works!!! As they say in Star Wars "May the force be with you"
|
