Number of Mondays between two dates

  • Andrey

    SSChasing Mays

    Points: 628

    declare @dt1 date  = '20190501';
    declare @dt2 date = '20190522';

    with dates as
    (
    select @dt1 [d], datename(dw,@dt1) [n]
    union all
    select dateadd(dd,1,d),datename(dw,dateadd(dd,1,d))
    from dates
    where d<=@dt2
    )
    select
    count(1) [Mondays]
    from dates
    where n='Monday'
  • Sergiy

    SSC Guru

    Points: 109668

    Look at the progress we've got in SQL over the years!

    what used to be 13 years ago a short inline formula using system functions and integer arithmetic is now implemented with a recursive CTE combined with a string comparison in WHERE clause, which adds huge computing overheads caused by collation rules.

    No wonder no server is big enough nowadays for a data warehouse of a size larger than an Excel spreadsheet. And no wonder MS are making so much money from Azure service - every solution like the one above adds a good buck to their bill, and man - aren't they all over the places?

     

  • Jeff Moden

    SSC Guru

    Points: 994546

    Sergiy wrote:

    Look at the progress we've got in SQL over the years! what used to be 13 years ago a short inline formula using system functions and integer arithmetic is now implemented with a recursive CTE combined with a string comparison in WHERE clause, which adds huge computing overheads caused by collation rules. No wonder no server is big enough nowadays for a data warehouse of a size larger than an Excel spreadsheet. And no wonder MS are making so much money from Azure service - every solution like the one above adds a good buck to their bill, and man - aren't they all over the places?  

     

    Oh lordy!  After reading that, I almost killed myself by trying to hold it in.  Gotta let it go!

     

    BWAAAAA-HAAAAAA-HAAAAA-HAAAAA!  HOOOOOOOOOIEEEEEEEEE!!!!  ROFLMAO!!!!

     

    Thanks for the laugh, Sergiy!  Needed it especially after this migration thing they did.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • vladdraco

    Newbie

    Points: 1

    I did sth like below to count Mondays in a month where the month is calculated from the date passed as parameter to the function:

    CREATE FUNCTION fn_GetNumMondaysInMonth (@Date datetime) RETURNS INT AS

    Begin

    DECLARE

    @StartDate datetime,

    @EndDate datetime,

    @count int

    SET @StartDate = DATEADD(DAY, 1, EOMONTH(@Date, -1))

    SET @EndDate = EOMONTH(@Date)

    SET @count = 0

    While @StartDate != @EndDate

    Begin

    Declare @a int

    set @a = datepart(dw,@StartDate)

    if @a =2

    begin

    set @count = @count + 1

    end

    set @StartDate = DateAdd(Day,1,@startdate)

    end

    RETURN @count

    end

    Cheers 🙂

  • Jeff Moden

    SSC Guru

    Points: 994546

    Sergiy wrote:

    Look at the progress we've got in SQL over the years!

    what used to be 13 years ago a short inline formula using system functions and integer arithmetic is now implemented with a recursive CTE combined with a string comparison in WHERE clause, which adds huge computing overheads caused by collation rules.

    No wonder no server is big enough nowadays for a data warehouse of a size larger than an Excel spreadsheet. And no wonder MS are making so much money from Azure service - every solution like the one above adds a good buck to their bill, and man - aren't they all over the places?

    And it's not over yet.  Look at the latest function in the post above this one.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • DesNorton

    SSC-Insane

    Points: 22641

    Jeff Moden wrote:

    Sergiy wrote:

    Look at the progress we've got in SQL over the years!

    what used to be 13 years ago a short inline formula using system functions and integer arithmetic is now implemented with a recursive CTE combined with a string comparison in WHERE clause, which adds huge computing overheads caused by collation rules.

    No wonder no server is big enough nowadays for a data warehouse of a size larger than an Excel spreadsheet. And no wonder MS are making so much money from Azure service - every solution like the one above adds a good buck to their bill, and man - aren't they all over the places?

    And it's not over yet.  Look at the latest function in the post above this one.

     

    Not to mention datepart(dw,@StartDate), which is dependent on DATEFIRST

Viewing 6 posts - 31 through 36 (of 36 total)

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