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;
SELECT LAST_DAY(ADD_MONTHS(TO_DATE('31-DEC-2008'), 2))+1 FROM dual;
SELECT LAST_DAY(ADD_MONTHS(TO_DATE('31-DEC-2009'), 2))+1 FROM dual;
Where's the problem?
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Jul 13 2006 - 11:11:51 CDT
