Re: calendar.sql

"Paul Drake" <bdbafh_at_gmail.com> wrote in message news:910046b4.0411231209.5ee76141_at_posting.google.com...
> "Oradba Linux" <techiey2k3_at_comcast.net> wrote in message
news:<eFeod.652457$8_6.498621_at_attbi_s04>...
> > From connor mcdonald's mastering oracle pl/sql , i am not able to
understand
> > the logic for using the colum p. The query is below.
> >
> > select
> > max(decode(dow,1,d,null)) Sun,
> > max(decode(dow,2,d,null)) Mon,
> > max(decode(dow,3,d,null)) Tue,
> > max(decode(dow,4,d,null)) Wed,
> > max(decode(dow,5,d,null)) Thu,
> > max(decode(dow,6,d,null)) Fri,
> > max(decode(dow,7,d,null)) Sat
> > from (
> > select rownum d ,
> >
> >

rownum-2+to_number(to_char(trunc(to_date('&CALDATE','DD-MON-YYYY'),'MM'),'D'
> > ))p,
> >

to_char(trunc(to_date('&CALDATE','DD-MON-YYYY'),'MM')-1+rownum,'D')
> > dow
> > from all_objects
> > where rownum <=to_number(to_char(last_day(to_date(sysdate)),'DD'))
> > )
> > group by trunc(p/7)
> > /
>
> that looks like a giant NLS pothole waiting to get hit.
> you might want to consider anchoring to a specific day as being the
> start of the week, as day of week is nls specific.
>
> feel free to ignore this comment - but - its one of those
> internationalization gotchas that you're far better off to code for
> from the start.
>
> -bdbafh

Also, you must specify a proper calendar month, otherwise the results are wrong. For example, if you specify 23-NOV-2004 thru 31-DEC-2004, you get December, but not the 31st, and not in the right order. Received on Tue Nov 23 2004 - 14:44:57 CST