Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL How to get the monday of every week RE: How to get the monday of every week

  • June 22, 2017 at 3:45 pm

    #1947859

    Jeff Moden - Thursday, June 22, 2017 3:09 PM

    komal145 - Thursday, June 22, 2017 1:55 PM

    Thanks everyone was able to get the monday's from Getdate()

    SELECT DATEADD(wk, -5, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
    SELECT DATEADD(wk, -4, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
    SELECT DATEADD(wk, -3, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
    SELECT DATEADD(wk, -2, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
    SELECT DATEADD(wk, -1, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
    SELECT DATEADD(wk, 0, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))

    So you have 15-16 lines of nearly identical hard-coded code in your code to solve your original problem.  While it will certainly work fine to solve your original problem, I strongly recommend you go back an look at some of the solutions offered and figure out what they actually do so that when you run into a similar problem with thousands of returned rows required, you'll be able to handle it without thousands of hard coded rows.

    Jeff's point here is an important one.  What happens when you want to make a table of Mondays for a year's worth of attendance?  What if you want to amortize money over a 30-year loan?  What if you want to track a star across the sky for 1000 years?  What if you want to try something even more astronomical like calculating how much it would take to pay off the national debt at a base of 5000 years? 😛

    Do yourself a favor up front and think about the set-based approach.  Using a function will save you keystrokes now and a lot of headache later.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions