---

Oracle / PLSQL: Function to return a monthly period

Question: How can I create a function that returns the period that correspond to a month?

A period starts on the first Monday of the month, and ends on a Sunday. If the Sunday does not fall on the 30th or 31st, the procedure should choose the first Sunday of the next month.

For example, the function should return something like this (startdate - enddate):

Oct 6, 2003 - Nov 2, 2003

Answer: Below is a function that accepts as input a date value formatted as 'yyyy/mm/dd'. The function takes this date and returns the period that the date falls within.

create or replace function get_period (pDate varchar2)
   return varchar2
is
   v_period_start date;
   v_period_end date;
   v_check_date date;

begin

   /* Determine the 1st of the month */
   v_check_date := TRUNC(TO_DATE(pDate, 'yyyy/mm/dd'),'MM');

   /* Find first monday */
   loop
      exit when TO_NUMBER(TO_CHAR(v_check_date,'d')) = 2;
      v_check_date := v_check_date + 1;
   end loop;

   v_period_start := v_check_date;

   /* Determine last sunday of current month */
   v_period_end := v_period_start + 27;

   /* Take the sunday in next month if the sunday falls */
   /* on the 29th or earlier */
   if TO_NUMBER(TO_CHAR(v_period_end, 'dd')) < 30 then
      v_period_end := v_period_end + 7;
   end if;

   return v_period_start || ' - ' || v_period_end;

end get_period ;