Re: help with a monthly rollup
- From: TESTAJ3@xxxxxxxxxxxxxx
- To: ricks12345@xxxxxxxxx
- Date: Tue, 13 May 2008 13:54:54 -0400
i always did the last day of the month as the first day of a particular
month - 1, that way leap year was taken into account automagically or you
can use: LAST_DAY(date)
LAST_DAY returns the date of the last day of the month that contains date.
The return type is always DATE, regardless of the datatype of date.
joe
---------------------------------------
You can have it: Fast, Right or Cheap, pick 2 of the 3.
Fast + Right is Expensive
Fast + Cheap will be incorrect.
Right + Cheap will take a while.
"Rick Ricky" <ricks12345@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
05/13/2008 01:44 PM
Please respond to ricks12345@xxxxxxxxx
From
"Rick Ricky" <ricks12345@xxxxxxxxx>
To
oracle-l@xxxxxxxxxxxxx
cc
Subject
help with a monthly rollup
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
Other related posts:
