I have a table that contains dates , For each date I need to return a reference date: same day of the same number of the week but during the previous year.
For example let's suppose today is 03-03-2023 , the day is friday and the number of the week is 5 I want as a resulat the date of friday in 2022 during the week number 5.
I have tried this formula but it didn't give me a good result
SELECT DATEADD(day, (DATEPART(week, @now) * 7 DATEPART(weekday, @now)) - (DATEPART(week, DATEADD(year, -1, @now)) * 7 DATEPART(weekday, DATEADD(year, -1, @now))), DATEADD(year, -1, @now))
Any help will be appreciated.
Best regards,
CodePudding user response：
Try this:
SELECT DATEADD(WEEK, -52, CAST(GETDATE() AS DATE))
CodePudding user response：
If you can use the ISO weeks definition, you can try the following.
CASE
WHEN DATEPART(iso_week, DATEADD(week, -52, @Date)) = DATEPART(iso_week, @Date)
THEN DATEADD(week, -52, @Date)
ELSE DATEADD(week, -53, @Date)
END
For about 80% of the cases, the -52 calculation will yield the correct result, otherwise (with one exception noted below) -53 gives the correct answer.
The only exceptional case is when the reference date is in ISO week 53 and the prior year only has 52 weeks. in that case, it will end up in week 52 of the prior year, which may be a reasonable accommodation.
If you wish to use a different week definition I believe the above can be adapted to week instead of iso_week and possibly use SET DATEFIRST to adjust the week boundaries.
If you don't care about ISO weeks or any other week-numbering scheme, and just want to get the closest date one year-ago on having the same day-of-week, then Nayanish's posted is the simple answer. If you also want to always guarantee that year is offset by 1, then the following adjustment can be made:
CASE
WHEN DATEPART(year, DATEADD(week, -52, @Date)) < DATEPART(year, @Date)
THEN DATEADD(week, -52, @Date)
ELSE DATEADD(week, -53, @Date)
END