List of days in a month

[stuartgmilton]

Hi,

I would like to create a SQL statement which would return a list of the days in the current month. ie for January it would return a list of numbers from 1 to 31.

I do not however wish to do this by creating a new table or view!

Any ideas??

Stuart

 

[nagornyi]

select day from
(
select 1 day from dual union
select 2 from dual union
select 3 from dual union
select 4 from dual union
select 5 from dual union
select 6 from dual union
select 7 from dual union
select 8 from dual union
select 9 from dual union
select 10 from dual union
select 11 from dual union
select 12 from dual union
select 13 from dual union
select 14 from dual union
select 15 from dual union
select 16 from dual union
select 17 from dual union
select 18 from dual union
select 19 from dual union
select 20 from dual union
select 21 from dual union
select 22 from dual union
select 23 from dual union
select 24 from dual union
select 25 from dual union
select 26 from dual union
select 27 from dual union
select 28 from dual union
select 29 from dual union
select 30 from dual union
select 31 from dual 
)
where day <= decode(to_number(to_char(sysdate,'MM')),1,31,2,28,3,31,4,30,5,31,6,30,7,31,8,31,9,30,10,31,11,30,12,31)

 

[stuartgmilton]

Superb, Thank you, thats exactly what I wanted.

Cheers

 

[nagornyi]

Just found another solution which is much shorter and automatically takes care of leap years:

select 
  rownum  day
from 
  all_objects 
where 
  rownum<=to_number(to_char((last_day(sysdate)), 'DD'))

Instead of all_objects any table can be used with at least 31 records.