Skip to Main Content
  • Questions
  • Query to return every other Wednesday

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 18, 2021 - 9:07 pm UTC

Last updated: February 19, 2021 - 10:10 am UTC

Version: 11g

Viewed 1000+ times

You Asked

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;

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

saravanakumar, March 25, 2021 - 10:00 am UTC

with df as(select rownum+trunc(to_date(sysdate),'year')-1 all_date from dual
connect by level<366),
fd as (select all_date ,to_char(all_date,'fmday') lk from df),
wq as (select rownum rm ,all_date,lk from fd where lk='wednesday')
select all_date,lk from wq where mod(rm,2)=1;

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.