Re: help with a monthly rollup

tahiti:

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"    FROM DUAL; SYSDATE Last Days Left
--------- --------- ----------
30-MAY-01 31-MAY-01 1 --romas

On 5/13/08, Rick Ricky <ricks12345_at_gmail.com> wrote:
>
> I need to rollup data by month.
>
> There are a couple ways to do this.
>
>
> I have a date field. So I can rollup with a to_char(mydate,'YYYYMM')
> this requires a where clause as followings
> where to_char(mydate,'YYYYMM') = pSomeMonth;
>
>
> This means I need a function based index to support this. We have alot of
> data, so where possible, I want to avoid adding more indexes. Is there a way
> to calculate what the last
> day of the month is?
>
>
> so I can use a between as follows
> where myDate between to_date(01-MON-YYYY') and
> to_date(LASTDAYOFMONTH-MON-YYYY')
>
>
> Since the last day of the month can be
> 28,29,30,31
>

--
http://www.freelists.org/webpage/oracle-l