sql server - SQL - pick current date from machine and compare the year

Question

Scenario 1: Current Year

Always code needs to pick

The last Sunday of January for the current year. For ex(31-01-2016)

Current code - picks 1st of Jan 2016

convert(date,DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0))

Scenario 2: Last Year

Always code needs to pick

The last Sunday of January for the Previous year. For ex(01-02-2015)

Current code - picks 1st of Jan 2015

convert(date,DATEADD(yy, DATEDIFF(yy, 0, dateadd(YEAR, - 1, getdate())), 0))

Instead of hard coding the date. I would like to pick date from machine and compare.

Week start from Sunday and ends Saturday. Any help please?

Answer 1

-- Sample Demonstrative Data/Test Results
Declare @YourTable table (SomeDate date)
Insert Into @YourTable values  ('2000-06-15'),('2001-06-15'),('2002-06-15'),('2003-06-15'),('2004-06-15'),('2005-06-15'),('2006-06-15')
,('2007-06-15'),('2008-06-15'),('2009-06-15'),('2011-06-15'),('2012-06-15'),('2013-06-15'),('2014-06-15'),('2015-06-15'),('2016-06-15')
,('2017-06-15'),('2018-06-15'),('2019-06-15'),('2020-06-15')

-- To Confirm Results
Select Year           = year(SomeDate)
      ,LastSundayDate = DateAdd(DD,-DatePart(DW,DateFromParts(Year(SomeDate),12,31))+1,DateFromParts(Year(SomeDate),12,31))
      ,LastSundayName = DateName(DW,DateAdd(DD,-DatePart(DW,DateFromParts(Year(SomeDate),12,31))+1,DateFromParts(Year(SomeDate),12,31)))
 From @YourTable

Returns

Year    LastSundayDate  LastSundayName
2000    2000-12-31      Sunday
2001    2001-12-30      Sunday
2002    2002-12-29      Sunday
2003    2003-12-28      Sunday
2004    2004-12-26      Sunday
2005    2005-12-25      Sunday
2006    2006-12-31      Sunday
2007    2007-12-30      Sunday
2008    2008-12-28      Sunday
2009    2009-12-27      Sunday
2011    2011-12-25      Sunday
2012    2012-12-30      Sunday
2013    2013-12-29      Sunday
2014    2014-12-28      Sunday
2015    2015-12-27      Sunday
2016    2016-12-25      Sunday
2017    2017-12-31      Sunday
2018    2018-12-30      Sunday
2019    2019-12-29      Sunday
2020    2020-12-27      Sunday