Re: Last Day Last Month - Query Assistance

From: kevin <majunyue_at_hotmail.com>
Date: Fri, 31 Jul 2009 18:34:07 -0700 (PDT)
Message-ID: <fc63a30b-9ebe-441c-9722-0d64cfdd8936_at_k13g2000prh.googlegroups.com>

On Aug 1, 8:27 am, master44 <trp..._at_gmail.com> wrote:
> On Jul 31, 6:07 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
> > On Jul 31, 4:36 pm, master44 <trp..._at_gmail.com> wrote:
>
> > > I put a query together to pull the last day of last month that has
> > > worked fine for the last 2 months, but suddenly failed today now that
> > > the date is the 31st of the month:
>
> > > SELECT TO_DATE(TO_CHAR(LAST_DAY(SYSDATE + numtoyminterval(-1,
> > > 'MONTH')), 'YYYY-MM-DD'), 'YYYY-MM-DD') FROM DUAL
>
> > > What am I doing wrong??
>
> > > Eunning this against a Oracle 10G database, sorry don't know the exact
> > > version.
>
> > select SYSDATE-numtoyminterval(1, 'MONTH') from dual
> >               *
> > ERROR at line 1:
> > ORA-01839: date not valid for month specified
>
> > Seems Oracle doesn't like some implicit conversion subtracting from
> > sysdate here.
>
> > jg
> > --
> > _at_home.com is bogus.http://pwnie-awards.org/2009/
>
> Yeah thats the error I get, but if you run it tomorrow it will return
> 07-31-2009; seems to just fail when the SYSDATE is the 31st. Is there
> a better / easier way to get the last day of last month?

select trunc(sysdate, 'month')-1 from dual Received on Fri Jul 31 2009 - 20:34:07 CDT