Number of Mondays between two dates

  • Andrey

    Old Hand

    Points: 366

    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: 109666

    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: 993661

    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

Viewing 3 posts - 31 through 33 (of 33 total)

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