Geeks With Blogs
The Quandary Phase This code was generated by a tool.

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.

Posted on Monday, October 20, 2008 4:38 PM | Back to top


Comments on this post: Oracle: Last Weekday In Month Function

# re: Oracle: Last Weekday In Month Function
Requesting Gravatar...
Thanks for that elegant little piece of code to calculate the last Friday of the month, it was just what I was looking for.
Left by Mark Hobba on Aug 22, 2010 1:30 PM

# re: Oracle: Last Weekday In Month Function
Requesting Gravatar...
I ran across search engines and find this blog that fulfill my needs. There is a thing I do not agree but It doesn't matter since I think it does not hurt the whole content.
Left by Indonesian Teak Furniture on Dec 07, 2010 5:27 AM

# re: Oracle: Last Weekday In Month Function
Requesting Gravatar...
Very good points you wrote here..Great stuff...I think you've made some truly interesting points.Keep up the good work.
Left by Free Adult Dating on Jan 13, 2011 9:15 PM

# re: Oracle: Last Weekday In Month Function
Requesting Gravatar...
Your work is very good and I appreciate you and hopping for some more informative posts. thank you for sharing great information to us
Left by buy viagra online on Mar 22, 2011 1:27 AM

Comments have been closed on this topic.
Copyright © Adam Pooler | Powered by: GeeksWithBlogs.net