TSQL Same Weekday Last Year, Next Year, and Year After

I know I can use the DATEADD function to take a date in TSQL and add a YEAR or subtract it. However how can I keep the weekday shifting.

For instance I can take 1/10/2018 and subtract a year to make it 1/10/2017 or add a year 1/10/2019. However all three of those days represent different weekdays.

What is the easiest method to see the same weekday a year in the future or a year in the past?

So extending the example above I would take 1/10/2018 and adding a year would return 1/9/2019 and subtracting a year would return 1/11/2017.
Ryan SimmonsBusiness Analyst IIIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Something like this?


SET @TestDate = '2018-01-10'
SELECT @TestDate [Test Date], DATEADD(YEAR, 1, @TestDate) [Plus 1 Year],
	DATEADD(DAY, DATEPART(WEEKDAY, @TestDate) - DATEPART(WEEKDAY, DATEADD(YEAR, 1, @TestDate)), DATEADD(YEAR, 1, @TestDate)) [Plus 1 Year Same Weekday],
	DATEADD(YEAR, -1, @TestDate) [Minus 1 Year],
	DATEADD(DAY, DATEPART(WEEKDAY, @TestDate) - DATEPART(WEEKDAY, DATEADD(YEAR, -1, @TestDate)), DATEADD(YEAR, -1, @TestDate)) [Minus 1 Year Same Weekday]

Open in new window

The one concern is when you flip over a month/year boundary.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
That works for me. Thanks!
Pawan KumarDatabase ExpertCommented:
It will NOT work. There are 2 columns for previous and next year
So how do you know which is to be used for any input date ?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Sounds like you need a SQL Server Calendar Table so you can code this logic once, and then just do a JOIN on the calendar table with WHERE clause criteria to get the exact dates....

>What is the easiest method to see the same weekday a year in the future
... especially with logic that can change such as 'same weekday'.
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
@Pawan - It's not meant to be a complete solution. It provides me with a direction and how best to use the functions available to achieve the required result.

@Jim Horn - That would be a great solution but I only have read access to the database. I will need functions to achieve the result I am looking for. If things change the functions can be modified.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.