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?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Something like this?

DECLARE @TestDate DATETIME

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.
0
 
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
That works for me. Thanks!
0
 
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 ?
0
 
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'.
1
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.