I'm using the below query to return the dates of every Wednesday for the 2021 calendar year.
How can I adjust this query to return every other Wednesday instead of every Wednesday?
select distinct next_day(to_date('01-01-2021','mm-dd-yyyy')-1+level,'Wednesday') All_WEDS
from dual
connect by level <= floor(to_date('12-31-2021','mm-dd-yyyy') - to_date('01-01-2021','mm-dd-yyyy'))
order by All_WEDS;
There's no need to fetch every day then take the distinct values. You just need the number of weeks and a row for every other week with a Wednesday.
Find this by:
* Calculating the days between the first Wednesday of the year and the last day
* Dividing this by 14
* Taking the ceiling of this
Getting every other Wednesday is then simply a matter of finding the first of the year and add 14 days to this for each row:
alter session set nls_date_format = ' DD Mon YYYY ';
select next_day (
to_date ( '01-01-2021','mm-dd-yyyy') - 1, 'Wednesday'
) + (
( level - 1 ) * 14
) all_weds
from dual
connect by level <= ceil ( (
add_months (
to_date ( '01-01-2021','mm-dd-yyyy'), 12
) - next_day (
to_date ( '01-01-2021','mm-dd-yyyy') - 1, 'Wednesday'
)
) / 7 / 2
)
order by all_weds;
ALL_WEDS
06 Jan 2021
20 Jan 2021
03 Feb 2021
17 Feb 2021
03 Mar 2021
17 Mar 2021
31 Mar 2021
14 Apr 2021
28 Apr 2021
12 May 2021
26 May 2021
09 Jun 2021
23 Jun 2021
07 Jul 2021
21 Jul 2021
04 Aug 2021
18 Aug 2021
01 Sep 2021
15 Sep 2021
29 Sep 2021
13 Oct 2021
27 Oct 2021
10 Nov 2021
24 Nov 2021
08 Dec 2021
22 Dec 2021