Get current, Next & Previous month of date as output in ORACLE SQL function

Get current, Next & Previous month as output in ORACLE SQL

Following will give the output from current data by using SYSDATE function of Oracle, You can also change according to your need. Output will provide in word and number format as below:

Get the Month in Words format

--Get the Current Month
SQL> Select (to_char(sysdate,'MON')) "CURR_MONTH" from dual;
CURR_MONTH
---------------------
SEP

--Get the Next Month from Current Date
SQL> Select To_char(ADD_MONTHS(sysdate,1),'MON') "NEXT_MONTH" from dual;
NEXT_MONTH
---------------------
OCT


--Get the Previous Month from Current Date
SQL> Select To_char(ADD_MONTHS(sysdate,-1),'MON') "PREV_MONTH" from dual;
PREV_MONTH
---------------------
AUG

Get the Month in Number format

-- Get the Current Month
SQL> Select (to_char(sysdate,'MM')) "CURR_MONTH" from dual;
CURR_MONTH
---------------------
09

-- Get the Next Month with current date
SQL> Select To_char(ADD_MONTHS(sysdate,1),'MM') "NEXT_MONTH" from dual;
NEXT_MONTH
---------------------
10

--Similarly without ADD_MONTHS function:
Select Case when (to_char(sysdate,'MM')+1) > 12 then '01' else to_char((to_char(sysdate,'MM')+1)) end "NEXT_MONTH" from dual;

--Get the Previous Month
SQL> Select To_char(ADD_MONTHS(sysdate,-1),'MM') "PREV_MONTH" from dual;
PREV_MONTH
---------------------
08

--Similar output without using ADD_MONTH function:
Select Case when (to_char(sysdate,'MM')-1) = 0 then '12' else to_char((to_char(sysdate,'MM')-1)) end "PREV_MONTH" from dual;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.