create table #temp (date date)
declare @X date
set @X = '2016-7-01'
declare @Y date
set @Y = cast (getdate() as date)
if (datename(WEEKDAY,@X) = 'Sunday')
insert into #temp values (@X)
set @X = cast(((cast(@X as datetime))+1)as date)
select * from #temp
drop table #temp
You can use a CTE to create a numbers table. You can then use the numbers table to get your dates like so:
Declare @Startdate Datetime = '2016-07-01' Declare @EndDate Datetime = '2016-08-29' ;with N0 as (SELECT 1 as n UNION ALL SELECT 1) ,N1 as (SELECT 1 as n FROM N0 t1, N0 t2) ,N2 as (SELECT 1 as n FROM N1 t1, N1 t2) ,N3 as (SELECT 1 as n FROM N2 t1, N2 t2) ,N4 as (SELECT 1 as n FROM N3 t1, N3 t2) ,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N4) SELECT DATEADD(day,num-1,@startdate) as thedate FROM nums WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1 and datename(WEEKDAY, DATEADD(day,num-1,@startdate)) = 'Sunday'
Each table (
nums) effectively multiplies the number of rows in the previous 'table', so you end up with 65,536 rows of numbers in
nums (you can do less or more by adding or removing table
NX as required). Then, use the numbers table to add days to your start date(
SELECT DATEADD(day,num-1,@startdate) as thedate) , where the dates returned are in your date range, and the
weekday is Sunday.
Also, because the numbers in
nums start at 1, we use
nums-1 in our select, so as to avoid skipping over the first date in our series, effectively giving us
DATEADD(day, 0, @startdate) in our first row.