0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for committed Christians, experts in Christianity and those interested in learning more

SET DATEFIRST 1;
DECLARE @sDate date
DECLARE @eDate date;
DECLARE @result NVARCHAR(MAX);
SET @sDate = '2018-01-01';
SET @eDate = '2025-12-31';

;with GetDates As  
(  
    Select DATEADD(day, -1, @sDate) as TheDate
    UNION ALL  
    Select DATEADD(day, 1, TheDate) as TheDate from GetDates  
    where TheDate < @eDate

)
SELECT 
  @result = (
    SELECT 
      CONVERT(NVARCHAR(6), DATEPART(year, TheDate)) +','
    FROM GetDates GROUP BY DATEPART(week, TheDate), DATEPART(year, TheDate) 
      HAVING Count(DATEPART(year, TheDate)) = 7 AND DATEPART(week, TheDate) = '1' 
      FOR xml path('')
  )
option (maxrecursion 0);
SELECT 
  @result as Year;

fork query

edited aug 4 20

created aug 1 20 anonymous