Re: Last Day Last Month - Query Assistance

From: master44 <trpost_at_gmail.com>
Date: Fri, 31 Jul 2009 17:27:00 -0700 (PDT)
Message-ID: <82d160d8-b1ff-41ca-a7e6-3b5e504f227f_at_j21g2000yqe.googlegroups.com>

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? Received on Fri Jul 31 2009 - 19:27:00 CDT