SQL & PL/SQL

Hi,

Generally financial year end will be 31st Dec. I have a situation where financial year end can be last day of any of the 12 months in a year. So based on that the quarter start dates, semi annual start dates vary.

Example: Financial Year End Date is 31-AUG-2008

Quarters

01-Sep-2007 to 30-Nov-2007
01-Dec-2007 to 29-Feb-2008
01-Mar-2008 to 31-May-2008
01-Jun-2008 to 31-Aug-2008

Semi Annual Start Dates

01-Sep-2007
01-Mar-2008

I have the following query to find the Quarter Start Dates no matter what month does the financial year end falls in

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MM'),
                     MOD (TO_CHAR (TO_DATE (<current fiscal year end>, 'DD-MON-YYYY'),
                                   'MM'
                                  ),
                          3
                         )
                   - 2
                  )
  FROM DUAL;

Example:
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MM'),
                     MOD (TO_CHAR (TO_DATE ('31-AUG-2008', 'DD-MON-YYYY'),
                                   'MM'
                                  ),
                          3
                         )
                   - 2
                  )
  FROM DUAL;

ADD_MONTHS(
-----------
01-MAR-2008

Likewise I am trying to write a query to find the Semi Annual Start Dates. Can any one help me with this.

Thanks