Re: Last Day Last Month - Query Assistance
From: master44 <trpost_at_gmail.com>
Date: Sat, 1 Aug 2009 13:32:39 -0700 (PDT)
Message-ID: <d90f2958-570b-4cfa-becd-b0b4511cdff4_at_i4g2000prm.googlegroups.com>
On Aug 1, 8:10 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -
Date: Sat, 1 Aug 2009 13:32:39 -0700 (PDT)
Message-ID: <d90f2958-570b-4cfa-becd-b0b4511cdff4_at_i4g2000prm.googlegroups.com>
On Aug 1, 8:10 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -
Thanks to all who replied, this looks to work:
last_day(add_months(sysdate,-1))
I was stuck on using numtoyminterval, but this is a much simpler approach. Thanks! Received on Sat Aug 01 2009 - 15:32:39 CDT