Method 1:
Select dateadd (day, X, col), 'tuesday' from
(
Select cast ('2017-1-1 'As datetime) as COL
) A cross join
(
Select top 365 b8. I + b7. I + b6. I + b5. I + b4. I + b3. I + b2. I + b1. I + b0. I x
From (select 0 I Union all select 1) B0
Cross join (select 0 I Union all select 2) B1
Cross join (select 0 I Union all select 4) B2
Cross join (select 0 I Union all Select 8) B3
Cross join (select 0 I Union all select 16) B4
Cross join (select 0 I Union all select 32) B5
Cross join (select 0 I Union all select 64) B6
Cross join (select 0-i Union all select 128) B7
Cross join (select 0 I Union all select 256) B8
Order by 1
) B
Where datepart (DW, dateadd (day, X, col) = 3
Method 2:
/*
Function: calculate all the days of a week (such as Monday) in a certain period of time.
DESIGN: OK _008
Time: 2006-10
*/
Declare @ date datetime
Declare @ startdate datetime
Declare @ enddate datetime
Declare @ weekday int
Declare @ I int
Set datefirst 7 -- set the first day of each week
Set @ startdate = '2014-01-01 '-- start date of the statistics
Set @ enddate = '2014-12-31 '-- end date of statistics
Set @ weekday = 2 -- depends on the actual @ datefirst. Generally, the default value is 7. For example, when @ startdate = '2017-01-01 ', @ weekday = 3 indicates Tuesday.
Set @ I = datepart (weekday, @ startdate)
Print 'day 1 per week setting @ datefirst: '+ Cast (@ datefirst as nvarchar (1 ))
Print 'Day of the week corresponding to the start date: '+ Cast (@ I as nvarchar (1 ))
If (@ I <= @ weekday and @ I <7)
Set @ I = @ weekday-@ I
Else if (@ I <= @ weekday and @ I = 7)
Set @ I = @ I-@ weekday
Else
Set @ I =datefirst-@ I + @ weekday
Set @ date = dateadd (day, @ I, @ startdate)
While @ date <= @ enddate
Begin
If (@ startdate <= @ date) print convert (nvarchar (10), @ date, 121)
Set @ date = dateadd (week, 1, @ date)
End
Go
/* = Running result = */
/*
@ Datefirst: 7
The start date corresponds to the day of the week: 1
2006-01-02
2006-01-09
......
2006-12-18
2006-12-25
*/