I have two parameters MONTH and YEAR, how get all date?
Eg. YEAR = 2021, MONTH = 8
Date ------ 01.08.2021 02.08.2021 ..... 31.08.2021
Advertisement
Answer
select dt + level - 1 as date_
from (select to_date(to_char(:year , 'fm0000') ||
to_char(:month, 'fm00'), 'yyyymm') as dt from dual)
connect by level <= add_months(dt, 1) - dt
;
This is almost the same as MT0’s answer, with a few minor differences and one that is not entirely minor.
The to_date function assumes a default of first day of the month, so it is not necessary to explicitly concatenate '01' to the year and month (although perhaps doing so makes the code easier to read for beginner programmers). In my opinion, that’s just a matter of taste.
I separated the computation of the first day of the month into a subquery. No worries, the optimizer will merge it into the outer query, so there is no efficiency cost – but the code will be easier to maintain.
The non-trivial difference is in the connect by clause. Even though mathematically the formula is equivalent to
dt + level - 1 < add_months(dt, 1)
or, better (still equivalent!)
dt + level <= add_months(dt, 1)
in terms of processing they are not equivalent. If written in the form above (previous line of code), for each value of level, the runtime will perform a date arithmetic calculation followed by a date comparison.
On the other hand, by solving the inequality for level (as I did in my query), the date calculation is performed just once (rather than once for every row), and the comparison is simply level <= some calculated number.
Perhaps in this problem “efficiency” plays no role, but as a matter of good coding, we should “solve for level” whenever possible, for the reason I just gave.