# I want only Sunday dates with next 18 weeks

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

• Commented Dec 14, 2022 at 8:50
• Please post what you have tried Commented Dec 14, 2022 at 9:25

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
[day],
((DATEDIFF([day], '19000107', @MeasureDate) / 7) * 7) + 7,
'19000107'
) AS SundayDate
UNION ALL
FROM   Cte
WEEK,
@NumberOfWeeks,
@MeasureDate
))
SELECT *
FROM   Cte;
``````

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!