Solution for Get all date from month – Oracle SQL
is Given Below:
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
I’m a fan of recursive CTEs, because they are standard SQL. In Oracle, you can use one like this:
with cte(dte) as ( select to_date('2020' || '8', 'YYYYMM') -- the two parameters are '2020' and '8' from dual union all select dte + interval '1' day from cte where dte < last_day(dte) ) select * from cte;
Here is a db<>fiddle.
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.
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.
Assuming you pass in the bind variables
:month, then you can use a hierarchical query:
SELECT TO_DATE( TO_CHAR(:year, 'FM0000') || TO_CHAR(:month, 'FM00') || '01', 'YYYYMMDD' ) + LEVEL - 1 AS "Date" FROM DUAL CONNECT BY TO_DATE( TO_CHAR(:year, 'FM0000') || TO_CHAR(:month, 'FM00') || '01', 'YYYYMMDD' ) + LEVEL - 1 < ADD_MONTHS( TO_DATE( TO_CHAR(:year, 'FM0000') || TO_CHAR(:month, 'FM00') || '01', 'YYYYMMDD' ), 1 )