Just a quickie: today I found need for a PL/SQL function to calculate the 'Sale Month' for a given transaction. The organisation in question derives their sale month from the last Friday of the month: anything before the last Friday is in the current calendar month, anything after the last Friday of the month should appear as the following month.
So, to get the last Friday of the month, I used the following:
1: create or replace FUNCTION "FN_GET_SALE_MONTH"
2: (
3: param_dateValue date
4: ) RETURN date
5: IS
6: var_lastFriday date;
7: var_result date;
8: BEGIN
9:
10: IF param_dateValue IS NOT null THEN
11:
12: --calculate the last friday of the month
13: SELECT LAST_DAY(param_dateValue) - MOD((CAST(TO_CHAR(LAST_DAY(param_dateValue), 'D') AS number) + 9), 7)
14: INTO var_lastFriday FROM dual;
15:
16: --if the specified date is after the last friday, return the following month as sale month
17: IF param_dateValue > var_lastFriday THEN
18: var_result := TRUNC(LAST_DAY(param_dateValue) + 1, 'MM');
19: ELSE
20: --otherwise return the same month
21: var_result := TRUNC(param_dateValue, 'MM');
22: END IF;
23:
24: END IF;
25:
26: RETURN var_result;
27:
28: END;
The above code accepts a single date parameter, which is the date for which the Sale Date should be derived. It then gets the last day in the month, and calculates how many days (if any) need to be subtracted from this to get to the last Friday. Finally, it compares the last Friday calculated date to the date passed in, and sets the sale month accordingly.
You could of course achieve the same effect with a while loop: get the last day of the month, then keep subtracting till you hit a Friday, then compare to the sale date, but this seems slightly more elegant.