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
--
@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?
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.
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.
select trunc(sysdate, 'month')-1 from dual
>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
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 --
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!