Re: Find First of Month

DA Morgan wrote:
> Jeroen van den Broek wrote:
> > "DA Morgan" <damorgan_at_psoug.org> schreef in bericht
> > news:1152739928.787663_at_bubbleator.drizzle.com...
> >> FireGeek wrote:
> >>> Looking for an SQL statement to return the first of the month 90 days
> >>> following a given date. So, if my date is 04/15/2006 I would like
> >>> 08/01/2006 returned.
> >>>
> >>> Help anyone?
> >>>
> >>> THANKS!!!
> >>>
> >>> FireGeek
> >> SELECT LAST_DAY(ADD_MONTHS(SYSDATE, 2))+1
> >> FROM dual;
> >>
> >
> > Doesn't seem correct when your startdate is January 31th in a non-leap year.
> > Adding 90 days, as OP requested, would come up with May 1th, which would
> > also be the definitive answer, as it happens to be the first of the month.
> > Your ADD_MONTHS would come up with March 31th, and as this is the last day
> > of the month, LAST_DAY would not change the result, so finally adding an
> > extra day would give April 1th as the answer i.s.o. May 1th.
> >
> > Why not just add 90 days to the startdate, and then use one of the TRUNC
> > functions used on your site:
> >
> > SELECT TO_CHAR(TRUNC(SYSDATE + 90, 'MONTH'), 'DD-MON-YYYY HH:MI:SS')
> > FROM dual;

>


> SELECT LAST_DAY(ADD_MONTHS(TO_DATE('31-DEC-2006'), 2))+1 FROM dual;

> SELECT LAST_DAY(ADD_MONTHS(TO_DATE('31-DEC-2007'), 2))+1 FROM dual;

The OP used the expression "90 days following a given date". Nowhere in your solution this is accounted for. Just because he additionally asked for the 1th of the resulting month (or the next? see Maxim's reply), your solution will work most of the times, but like I said: not when starting at January 31th in a non-leap year, for the reason mentioned in my previous post. I can't make it any more clear than this.

-- 
Jeroen