SYSDATE - 1 'MM' for JAN showing 0 instead of 12 [message #658993] |
Tue, 03 January 2017 09:27 |
|
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 #659000 is a reply to message #658995] |
Tue, 03 January 2017 10:09 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
aceboku wrote on Tue, 03 January 2017 15:33My 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 |
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 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
aceboku wrote on Tue, 03 January 2017 16:33My 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.
|
|
|
|