Re: How do I change the default quaters in PL/SQL

Joe wrote:
> Hello Everyone,
>
> I am writing a pl/sql program and in the program I have to seperate the
> date in quaters. I know there is a way to do that:
>
> SQL> select * from tempe;
>
> TDATE
> -----------
> 26-DEC-2005
> 26-MAR-2006
> 01-JAN-2006
>
> SQL> select to_char(tdate, 'YYYY-Q') from tempe;
>
> TO_CHA
> ------
> 2005-4
> 2006-1
> 2006-1
>
> but the problem here is our quaters are different from the default
> quaters. Our quaters start from Mon and ends in Sun. If a month ends in
> a wed the previous sunday is used as end-date and moday is used for the
> next quater.
> Example:
> Moday Dec-26-05 - Sunday Mar-26-06 - 1st quater
> Moday Mar-27-06 - Sunday Jun-25-06 - 2nd quater
> Moday Jun-26-06 - Sunday Sep-24-06 - 3rd quater
> Moday Sep-25-06 - Sunday Dec-3106 - 4th quater
>
> so the first record in the tempe should be 1st quater of 06 according
> to our busines rule. How do I do that. Your kindness in helping me is
> greatly appreciated.
> Thanks,
>
> Joe

You will need to write customized code or store a table of dates. I'd personally suggest writing a pipelined table function in the manner of the one in Morgan's Library (www.psoug.org) under pipelined table functions ... scroll down to: "Generate Date List."

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org