Link to home
Start Free TrialLog in
Avatar of Queennie L
Queennie L

asked on

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

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'

Open in new window




Please help.

Thank you.
Get-all-monday-for-the-past-2-years.xlsx
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

but I cannot get it to where it will show 2 years to current date week and I have to do it by year

so is that mean you want to select the Mondays from 1 Jan 2018 to 31 Dec 2020 ?
You may modify your query slightly:
DECLARE @EndOfThisYear DATE
	SET @EndOfThisYear = CAST( CAST(YEAR(GETDATE()) AS varchar(4)) + '-12-31' AS date)

DECLARE @BeginningOfThisYear DATE
	SET @BeginningOfThisYear = CAST( CAST(YEAR(GETDATE()) AS varchar(4)) + '-01-01' AS date)
	
	
SELECT
    YEAR(dt) Year, 
	dt as MondayDate, 
    datename(dw, dt) as MondayDateName  
FROM 
    (
        select 
			dateadd(d, row_number() over (order by name), @BeginningOfThisYear) as dt 
            from 
            sys.columns a
    ) as dates 
WHERE 
datepart(dw, dates.dt) = 2
AND dt BETWEEN @BeginningOfThisYear AND @EndOfThisYear

Open in new window

And now it should be easy to obtain values for whatever date and years range you need. You may simply extend @BeginningOfThisYear by to more past years and possibly add appropriate Year check into WHERE clause
SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The above query ignores the 1/1/2018.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Queennie L
Queennie L

ASKER

My apology. It is been awhile I did not login due to covid.
All of the queries are my solutions.
pcelba  is my solution.
slightwv (䄆 Netminder) is my solution.
Scott Pletcher  is my solution.

Thank you for all your help.

@Queennie,

It is up to you to select "This is the solution" and/or "This is helpful" for the post or posts that helped you answer your question.
@slightwv: I will do that. Thanks.