Number of Mondays between two dates

  • Sergiy, I believe the following is correct:

    select case when

    Datediff(dd, @startDate, @endDate - (@@Datefirst+Datepart(dw,@endDate)-2) %7) < 0

    then 0 else

    Datediff(dd, @startDate, @endDate - (@@Datefirst+Datepart(dw,@endDate)-2) %7) / 7 + 1


    The division and modulo operations behave strangely for negative numbers, I think. E.g

    select (-1)/7

    select (-1) % 7

    is 0 and -1, respectively. I would expect it to be -1 and 6, respectively, and then your posted formula would work.


  • There is no way to get (-1)%7 in my formula.

    Both @@Datefirst and Datepart(dw,@endDate) are positive values, their SUM is >= 2.

    And for both dtes '2005-12-31' it returns 0 which is right - no Mondays between Saturday 31/12/2005 and Saturday 31/12/2005.

    But one thing I missed when copied script here:

    @StartDate must be shifted to Previous Monday as well:

    select Datediff(dd,

    @StartDate - (@@Datefirst+Datepart(dw,@StartDate)-2) %7,

    @EndDate - (@@Datefirst+Datepart(dw,@EndDate)-2) %7)

    / 7

    Code for TallyGenerator

  • What I meant was that you could easily get (-1)/7, although this wasn't clear from what I wrote.

    I assumed you wanted to add 1 to the formula you stated perviously. Anyway, it seems to return 0 whenever the two dates are the same.

    I think this is also the case with your new formula - you always get 0 when the two dates are the same

  • I mean get (-1)%7, not get (-1)/7

    And what you expect to get when 2 dates are the same?

    There are no Mondays between them, so it must be 0.

    What's wrong with that?

    Code for TallyGenerator

  • I see. No, nothing's wrong. You include the second date but not the first. I thought you included both dates (like I do in my query). I apologize, your query works perfectly.

    But... I think my query is slightly faster (replace '19000102' by '19000101' to get the same output as your query).


  • My query does not depend on DATEFIRST settings.

    Code for TallyGenerator

  • So I've noticed - neither does mine

  • What about server collation?

    Code for TallyGenerator

  • And you formula returns 1 Monday between '2005-02-19' and '2006-02-20' and between '2006-02-20' and '2006-02-21'.

    This Monday is included in both periods. Sounds not right.

    Code for TallyGenerator

  • My formula

    datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7

    doesn't depend on datefirst settings and server collation. Like I have said before, it returns the number of Mondays between @dateFrom and @dateTo, both days inclusive. '2006-02-20' is a Monday, as far as I know, and therefore it will return 1 for both periods.

    If you insist on not including the first date (but still including the second date), my formula is easily modified:

    datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000101', @dateFrom)/7

    I have tested that this formula has the same output as your formula for every single day of the year 2005 (365*365 test cases), so I guess both are correct. My formula, however, performs slightly better than your formula - at least, that was what I concluded in my test.


  • Sorry, I was looking on another your formula.

    Code for TallyGenerator

  • Yes, the first one wasn't correct for dates after '20060213', as Jeff Moden pointed out. My present formula isn't correct for dates before '19000101', but I guess that's all right


  • Hi - great posting and answers... I realize the posts are quite old - but hoping some of you experts are still available out here.

    I used the counting method in crystal reports and it seems pretty good- but I am intrigued by the formula. But - when I try the formula I do not get the same results.

    I am using the date range 2009-03-01 to 2009-03-31. Should have 5 Sundays, Mondays, Tuesdays and 4 of the rest of the week days.

    When I use the formula

    cnt:=datediff("d", date(1900,01,01), dt2)/7 - datediff("d", date(1900,01,02), dt1)/7

    I show 4.43. Does your formula assume it needs to be rounded up? I tried to run it in my sql plus - but it did not like "DATEDIFF". I ran a modified version (just subtracting the dates) - and it also yielded a fractional number -

    1 SELECT

    2 (TO_DATE('03/31/2009','MM/DD/YYYY')-TO_DATE('01/01/1900','MM/DD/YYYY'))/7 -

    3 (TO_DATE('03/01/2009','MM/DD/YYYY')-TO_DATE('01/01/1900','MM/DD/YYYY'))/7

    4 datediff

    5* FROM dual

    SQL> /







    Am I missing something. Were the divisions supposed to be truncated maybe?

    thanks in advance for your help.

    updated - tried with

    cnt:=truncate(datediff("d", date(1900,01,01), dt2)/7) -

    truncate(datediff("d", date(1900,01,02), dt1)/7)

    and intitial testing looks like that may be working (o - i only tested one thing - hopefully this is right....)


  • This might not be the most popular answer: a calendar table is terribly convenient to have around. I made one using the following structure:

    CREATE TABLE [dbo].[Calendar](

    [dt] [datetime] NOT NULL, --the actual date

    [isWeekend] [bit] NOT NULL,

    [isHoliday] [bit] NOT NULL,

    [day_desc] [nvarchar](50) NULL, --a description for holidays "New Year's Day", "Thanksgiving Day", etc.

    [Y] [smallint] NOT NULL, --year

    [M] [tinyint] NOT NULL, --month

    [D] [tinyint] NOT NULL, --day

    [DW] [tinyint] NOT NULL, --day of week [1-7]

    [JD] [smallint] NOT NULL, --Julian date [1-366]

    [monthname] [varchar](9) NOT NULL,

    [dayname] [varchar](9) NOT NULL, --translated day of week "Monday", "Tuesday", etc.



    [dt] ASC


    ) ON [PRIMARY]

    I then populated it out 50 years or so. I found a bunch of functions to define the holidays my company observes as well.

    Once you have a table like this in place the solution to your challenge (and lots of other challenges) becomes trivial. I consider this the calendar equivalent of my numbers utility table. In fact, I used my numbers table to help me initially populate this calendar table. It makes life easy.

    For me, the answer to "How many mondays between two dates?" looks like this:

    declare @startdate datetime

    declare @enddate datetime

    set @startdate = '01/01/2009'

    set @enddate = '01/01/2010'


    FROM dbo.calendar

    WHERE dt >= @startdate

    and dt <= @enddate

    and DW = 2

  • A different approach is reccursion: (Im using T-sql)

    DECLARE @startDate date = '2019-04-01',
    @endDate date = '2019-05-01'

    ;WITH cte AS (
    SELECT @startDate as date_
    SELECT CAST(DATEADD(day,1,date_) as date)
    FROM cte
    WHERE date_ < @endDate

    SELECT sum(case when datepart(dw,date_) = 2 then 1 else 0 end) as nrOfMondays
    FROM cte


Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply