Re: Last Day Last Month - Query Assistance

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 31 Jul 2009 18:27:26 -0700 (PDT)
Message-ID: <cc9c8b54-cefc-4291-9dcb-4baab46734de_at_24g2000yqm.googlegroups.com>

On Jul 31, 7: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.

You are trying too hard.

TRUNC(SYSDATE,'MM") returns the first day of the current month. The last day of last month is one less than the first day of the current month. Therefore, you will need:
SELECT
  TRUNC(SYSDATE,'MM')-1
FROM
  DUAL; TRUNC(SYS

---

30-JUN-09 And tomorrow it will be:
SELECT
  TRUNC(SYSDATE+1,'MM')-1
FROM
  DUAL; TRUNC(SYS

---

31-JUL-09 Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jul 31 2009 - 20:27:26 CDT