Re: Last Day Last Month - Query Assistance

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 1 Aug 2009 07:10:16 -0700 (PDT)
Message-ID: <81cb524e-43c8-498b-ba4e-682facef25ca_at_o32g2000yqm.googlegroups.com>

On Jul 31, 9:34 pm, kevin <majun..._at_hotmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Besides Kevin's suggestion you can also use last_day(add_months(sysdate,-1))

to shift back to the prior month then grap the last day.

HTH -- Mark D Powell -- Received on Sat Aug 01 2009 - 09:10:16 CDT