CODEWITHSUNDEEP
sqloracle-database
Dead Programmer
Dead Programmer

Reputation: 12585

List all the months using oracle sql

Guys, is there any better way to list all the months other than this:

select to_char(add_months(to_date('01/01/1000', 'DD/MM/RRRR'), ind.l-1), 'MONTH') as month_descr,
       ind.l as month_ind
  from dual descr,
       (select l
          from (select level l 
                  from dual 
                connect by level <= 12
               )
       ) ind
order by 2;

ANSWER: 

SELECT to_char(add_months(SYSDATE, (LEVEL-1 )),'MONTH') as months 
  FROM dual 
CONNECT BY LEVEL <= 1

ONE MORE QUESTION SEE BELOW

Also I want to list the previous two years including the current year. I wrote this sql query. Let me know if there is anything better.

select extract(year from sysdate) - (level-1) as years 
  from dual 
connect by level <=3 
order by years

Upvotes: 7

Views: 67306

Answers (6)

Chris Saxon
Chris Saxon

Reputation: 9875

Starting in Oracle Database 23ai, you can create an enum domain for the months o the year:

create usecase domain months_of_year as enum (
  january, february, march,
  april,   may,      june,    
  july,    august,   september,
  october, november, december
);

You can then query the enum like a regular table to see the month names and their values:

create usecase domain months_of_year as enum (
  january, february, march,
  april,   may,      june,    
  july,    august,   september,
  october, november, december
);

select * from months_of_year;

ENUM_NAME ENUM_VALUE
--------- ----------
JANUARY            1
FEBRUARY           2
MARCH              3
APRIL              4
MAY                5
JUNE               6
JULY               7
AUGUST             8
SEPTEMBER          9
OCTOBER           10
NOVEMBER          11
DECEMBER          12

Upvotes: 0

Prasant
Prasant

Reputation: 105

SELECT to_char(to_date( level,'mm'), 'MONTH') Months FROM DUAL CONNECT BY LEVEL <=12;

Regards, Prasant Sutaria

Upvotes: 3

Sandeep Mandloi
Sandeep Mandloi

Reputation: 1

 SELECT TO_CHAR(TO_DATE(rownum||'-'||rownum||'-'||'2013', 'DD-MM-YYYY'), 'Month') 
 FROM    all_objects 
 WHERE rownum < 13

Upvotes: 0

Doug Porter
Doug Porter

Reputation: 7897

select to_char(add_months(trunc(sysdate, 'yyyy'), level - 1), 'MONTH') months
  from dual
connect by level <= 12;

Returns:

MONTHS
--------------------
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER

12 rows selected.

Upvotes: 6

Rob van Wijk
Rob van Wijk

Reputation: 17705

Not better, but just a bit cleaner:

SQL>  select to_char(date '2000-12-01' + numtoyminterval(level,'month'),'MONTH') as month
  2     from dual
  3  connect by level <= 12
  4  /

MONTH
---------
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER

12 rows selected.

Regards, Rob.

Upvotes: 14

ksogor
ksogor

Reputation: 873

Yup.

1:

SELECT * FROM WWV_FLOW_MONTHS_MONTH;

2: (UPD:)

WITH MONTH_COUNTER AS (
  SELECT LEVEL-1 AS ID 
  FROM DUAL 
  CONNECT BY LEVEL <= 12
) 
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01/01/1000', 'DD/MM/RRRR'), ID),'MONTH') FROM MONTH_COUNTER;

Upvotes: 7

Related Questions