Re: Find First of Month

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 14 Jul 2006 09:10:10 -0700
Message-ID: <1152893850.700099@bubbleator.drizzle.com>

Jeroen van den Broek wrote:

> 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;
>> 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?
> 
> 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.

Isn't this what the OP 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."

That is not 90 days.

-- 
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 Fri Jul 14 2006 - 11:10:10 CDT