I want only Sunday dates with next 18 weeks

Question

I want only Sunday dates for example 11-12-2022 is Sunday and 18-12-2022 is Sunday and 25-12-2022 is Sunday etc... I want these records total 18sunday dates records

Answer 1

This script will calculate the next sunday and will show the dates of the following sundays, with a maximum of 18 weeks.

DECLARE @MeasureDate DATE = GETDATE();
DECLARE @NumberOfWeeks INT = 18;

WITH Cte
AS (SELECT
       DATEADD(
                 [day],
                 ((DATEDIFF([day], '19000107', @MeasureDate) / 7) * 7) + 7,
                 '19000107'
              ) AS SundayDate
    UNION ALL
    SELECT DATEADD([day], 7, Cte.SundayDate)
    FROM   Cte
    WHERE  DATEADD([day], 7, Cte.SundayDate) < DATEADD(
                                                         WEEK,
                                                         @NumberOfWeeks,
                                                         @MeasureDate
                                                      ))
SELECT *
FROM   Cte;

Answer 2

This script will give you the dates of the next 18 sundays, it uses a tally table to generate the rows needed which can be very useful when dealing with date ranges. You can learn more at: https://youtu.be/bVbOo8N-Rzc Where Magnus explains that concept far better than I ever could, I used much of his example so that it'll be easier to follow.

declare @start date = getdate() ; 
declare @end date = dateadd(week, 18,@start);

/* Here's the tally table bit */
with ten as(
select n from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(n)
), thousand as (
select top (datediff(day,@start, @end))
row_number() over(order by (select null)) as n
from ten t1
cross join ten t2
cross join ten t3
order by n)

/* And here we return the dates we want */
select cast(dateadd(day, n, getdate()) as date) as mySundays
from thousand
where datepart(weekday, dateadd(day, n, getdate())) = 1; /* Assuming sunday is weekday 1, change if needed */

Hope that helps!