Skip to Main Content
  • Questions
  • How to find all Mondays between two dates?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, meher.

Asked: July 31, 2018 - 7:00 pm UTC

Last updated: August 02, 2018 - 10:08 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked


I have to find all mondays between two date range which can be parameterized or coming from two different columns of a table.
Also need to generate a sql to get next 20 mondays from sysdate.

can you please help me to get sql query for these 2 requirements ?

and Chris said...

The next_day function is your friend here. Pass the day you want as the second parameter. And it'll return the date this falls on after the one you passed.

So you can use this to look for the next Monday. Note that if the date you pass is a Monday, it'll return the following Monday. Not the input date.

So how does this help you find all the Mondays between two dates?

With a bit of maths!

Find the first and last Monday in the range. Subtract the latter from the former. This gives you the total days between the Mondays.

Divide this value by seven to get the number of weeks covered. Then add one.

To find the first Monday, subtract one from the start date (to cover for starting on Monday). Then find the next Monday.

For the last Monday, subtract seven. And next_day the result of this

Which gives:

var start_date varchar2(10);
var end_date varchar2(10);

exec :start_date := '2018-01-01';
exec :end_date := '2018-01-31';

select ( 
         ( next_day ( ( to_date ( :end_date, 'yyyy-mm-dd' ) - 7 ), 'monday' ) - 
             next_day ( ( to_date ( :start_date, 'yyyy-mm-dd' ) - 1 ), 'monday' ) 
         ) / 7 
       ) + 1 number_of_mondays
from   dual;

NUMBER_OF_MONDAYS   
                  5 


So what about getting the next N Mondays?

First generate N rows with your favourite row generation method.

Then take the next Monday from sysdate (remembering to subtract one if you want to include the current date if it's Monday). For each row add the number of weeks elapsed since the first. Which is the row number minus one, times seven.

Giving:

select to_char ( sysdate, 'day' ) current_day,
       next_day ( sysdate, 'monday' ) + ( ( level - 1 ) * 7 ) mondays
from   dual
connect by level <= 10;

CURRENT_DAY   MONDAYS       
thursday      06-AUG-2018   
thursday      13-AUG-2018   
thursday      20-AUG-2018   
thursday      27-AUG-2018   
thursday      03-SEP-2018   
thursday      10-SEP-2018   
thursday      17-SEP-2018   
thursday      24-SEP-2018   
thursday      01-OCT-2018   
thursday      08-OCT-2018

Rating

  (1 rating)

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

Comments

Thanks for the solution..it works for me

meher, August 02, 2018 - 12:41 pm UTC

Hi,
Thank you so much !!

I got my required sql for my requirement.
so now I will be able to pass a start date and end date and get all the mondays along with the count.

Thanks a lot again !!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.