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

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1149263472.570580_at_bubbleator.drizzle.com...
: 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.eduion
: (replace x with u to respond)
: Puget Sound Oracle Users Group
: www.psoug.org

achhh!

simple date functions can be used to implement this algorithm

:> If a month ends in
: > a wed the previous sunday is used as end-date and moday is used for the
: > next quater.

[Quoted] not a complete description, but it sounds like some sort of rounding to the next or previous monday based on the day of week the quarter starts/ends on

so a combination of trunc, to_char, and next_day with a case or a decode should accomplish whatever the algorithm actually is ... which could then be encapsulated in a function

++ mcs Received on Fri Jun 02 2006 - 18:35:26 CEST