CODEWITHSUNDEEP
sqloracle-database
Pointer
Pointer

Reputation: 2186

Oracle - Get all days from month

How create select which return all days from month where month = PARAMETER_MONTH eg. 5 and year = extract(year from sysdate).

  1
  2
  3
  ..
  30
  31

Upvotes: 0

Views: 142

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

"Row generator" is the search keyword. For example:

SQL> with temp (col) as
  2    (select to_date(&par_month, 'mm') from dual)
  3  select to_number(to_char(col + level - 1, 'dd')) day
  4  from temp
  5  connect by level <= last_day(col) - col + 1
  6  order by day;
Enter value for par_month: 5

       DAY
----------
         1
         2
         3
         4
         5
         6
<snip>
        29
        30
        31

31 rows selected.

SQL>

TO_DATE function will convert entered value into the 1st day of that month in current year, so you don't have to worry about "and year = extract(year from sysdate)" you mentioned in question.

Upvotes: 2

Related Questions