troubleshooting Question

How to get every Mondays for the past 2 years to current date.

Avatar of Queennie L
Queennie L asked on
Microsoft SQL ServerSQL
10 Comments1 Solution118 ViewsLast Modified:
Hello Experts,

How to get every Mondays for the past 2 years to current date week.

This is the code I have so far but I cannot get it to where it will show 2 years to current date week and I have to do it by year: Please see attached.
DECLARE @EndOfLastYr DATE
	SET @EndOfLastYr = dateadd(DD, -1, dateadd(YY,datediff(yy,0,getdate()),0))

	DECLARE @EndOf2YrAgo DATE
	SET @EndOf2YrAgo = CAST(DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @EndOfLastYr), 0)) AS date)

	DECLARE @EndOf3YrAgo DATE
	SET @EndOf3YrAgo = CAST(DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @EndOf2YrAgo), 0)) AS date)
	
	
	
	
SELECT
    dt as MondayDate, 
    datename(dw, dt) as MondayDateName,
   
FROM 
    (
        select 
            dateadd(d, row_number() over (order by name), cast(@EndOf3YrAgo as datetime)) as dt 
            from 
            sys.columns a
    ) as dates 
WHERE 
datepart(dw, dates.dt) = 2
AND dt >= '01 Jan 2018' AND dt < '01 Jan 2019'
AND dt >= '01 Jan 2018' AND dt < '01 Jan 2019'



Please help.

Thank you.
Get-all-monday-for-the-past-2-years.xlsx
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros