Last Day Last Month - Query Assistance

master44

unread,

Jul 31, 2009, 7:36:41 PM7/31/09

to

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.

joel garry

unread,

Jul 31, 2009, 8:07:02 PM7/31/09

to

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
--
@home.com is bogus.
http://pwnie-awards.org/2009/

master44

unread,

Jul 31, 2009, 8:27:00 PM7/31/09

to

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?

John Hurley

unread,

Jul 31, 2009, 9:23:15 PM7/31/09

to

On Jul 31, 8:27 pm, master44 <trp...@gmail.com> wrote:

snip

> 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?

Try using google this question must have been asked and answered
thousands of times on the internet.

Charles Hooper

unread,

Jul 31, 2009, 9:27:26 PM7/31/09

to

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.

kevin

unread,

Jul 31, 2009, 9:34:07 PM7/31/09

to

select trunc(sysdate, 'month')-1 from dual

sybr...@hccnet.nl

unread,

Aug 1, 2009, 2:35:20 AM8/1/09

to

On Fri, 31 Jul 2009 17:27:00 -0700 (PDT), master44 <trp...@gmail.com>
wrote:

>Is there
>a better / easier way to get the last day of last month?

Use the LAST_DAY function?
Sorry to say so, but this happens when you never consult the SQL
reference manual.

--
Sybrand Bakker
Senior Oracle DBA

Mark D Powell

unread,

Aug 1, 2009, 10:10:16 AM8/1/09

to

> 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 --

master44

unread,

Aug 1, 2009, 4:32:39 PM8/1/09

to

> 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!