Re: Find First of Month

"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;

-- 
Jeroen
(sorry for accidently posting this to your personal email address earlier)