Home » SQL & PL/SQL » SQL & PL/SQL » SYSDATE - 1 'MM' for JAN showing 0 instead of 12 (Oracle )
SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #658993] Tue, 03 January 2017 09:27 Go to next message
aceboku
Messages: 3
Registered: January 2017
Junior Member
My script should return previous month using SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'MM') -1) FROM DUAL; But when this is used in month of January I get a result of 0 instead of 12. Outside of using the following case:

(CASE WHEN (TO_NUMBER(TO_CHAR(SYSDATE, 'MM') -1)) = 0 THEN 12 ELSE (TO_NUMBER(TO_CHAR(trunc(SYSDATE), 'MM') -1))END)

Is there another option or function that can be utilized?
Re: SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #658994 is a reply to message #658993] Tue, 03 January 2017 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

SQL> select add_months(sysdate,-1) from dual;

ADD_MONTH
---------
03-DEC-16

Re: SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #658995 is a reply to message #658993] Tue, 03 January 2017 09:33 Go to previous messageGo to next message
aceboku
Messages: 3
Registered: January 2017
Junior Member
My co-worker suggested following code. Thought I should share answer with the forum:
to_number(to_char(add_months(trunc(sysdate,'mm'),-1),'MM'))

Re: SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #658996 is a reply to message #658993] Tue, 03 January 2017 09:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Another solution:
orclz> select to_char(sysdate - interval '1' month,'mm') from dual;

TO
--
12

orclz>
Re: SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #658997 is a reply to message #658996] Tue, 03 January 2017 09:36 Go to previous messageGo to next message
aceboku
Messages: 3
Registered: January 2017
Junior Member
Thanks BlackSwan and John! Great simple comments/answers
Re: SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #659000 is a reply to message #658995] Tue, 03 January 2017 10:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
aceboku wrote on Tue, 03 January 2017 15:33
My co-worker suggested following code. Thought I should share answer with the forum:
to_number(to_char(add_months(trunc(sysdate,'mm'),-1),'MM'))

trunc is doing nothing useful there.

When trying to convert one date to another it's always safest to use date functions / date arithmetic on the date and then convert to another datatype for display once you've got the value you want.
Re: SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #659002 is a reply to message #658993] Tue, 03 January 2017 10:50 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Just for fun using mathematics using MOD function for getting the remainder of the division for the further easy processing.
I am also generating dates for all months for the immediate check of its correctness.
with my_date_table as ( 
    select to_date ( to_char(column_value, '00')||'-2016', 'MM-YYYY' ) my_date 
    from table( sys.odcinumberlist( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ) ) 
)
select my_date, extract( month from my_date ) this_month
  , mod( extract( month from my_date ) + 11 - 0, 12 ) + 1 also_this_month
  , mod( extract( month from my_date ) + 11 - 1, 12 ) + 1 prev_1_month
  , mod( extract( month from my_date ) + 11 - 2, 12 ) + 1 prev_2_month
from my_date_table
;
Re: SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #659003 is a reply to message #658995] Tue, 03 January 2017 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
aceboku wrote on Tue, 03 January 2017 16:33
My co-worker suggested following code. Thought I should share answer with the forum:
to_number(to_char(add_months(trunc(sysdate,'mm'),-1),'MM'))

If you want the month number don't use "TO_NUMBER(TO_CHAR" but:
SQL> select extract(month from add_months(sysdate,-1)) from dual;
EXTRACT(MONTHFROMADD_MONTHS(SYSDATE,-1))
----------------------------------------
                                      12
In addition, this is faster.


Re: SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #659004 is a reply to message #658996] Tue, 03 January 2017 12:52 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
John Watson wrote on Tue, 03 January 2017 10:34
Another solution
Bad solution. Will not work for most 31 of the month or for march 31, 30 and 29 if year isn't leap year.

SY.
Previous Topic: Partitions
Next Topic: JSON_TABLE concurrent access issue
Goto Forum:
  


Current Time: Thu Mar 28 06:48:28 CDT 2024