# How to choose every Monday and every Friday of the year

Here's one way (you may need to check which day of the week is the first, here I have Sunday as the first day of the week)

You can use a table with many rows (more than 365) before `CROSS JOIN`

to get start dates (talling table).

My sys columns have over 800 rows, you can use any other table or even `CROSS JOIN`

table for yourself to multiply the number of rows

Here I used a function `row_number`

to get the current row count and incremented the date by 1 day for each row:

```
select
dateadd(d, row_number() over (order by name), cast('31 Dec 2013' as datetime)) as dt
from sys.columns a
```

As a result of set dates, it is now trivial to check the day of the week using `datepart()`

```
SELECT
dt,
datename(dw, dt)
FROM
(
select
dateadd(d, row_number() over (order by name), cast('31 Dec 2013' as datetime)) as dt
from
sys.columns a
) as dates
WHERE
(datepart(dw, dates.dt) = 2 OR datepart(dw, dates.dt) = 6)
AND dt >= '01 Jan 2014' AND dt < '01 Jan 2015'
```

Edit:

Here's a SqlFiddle example

http://sqlfiddle.com/#!6/d41d8/21757

Edit 2:

If you want them on the same line, the days of the week are at least constant, you know that Friday is always 4 days after Monday, so do the same, but only look at Mondays, then add 4 days to Monday ..

```
SELECT
dt as MonDate,
datename(dw, dt) as MonDateName,
dateadd(d, 4, dt) as FriDate,
datename(dw, dateadd(d, 4, dt)) as FriDateName
FROM
(
select
dateadd(d, row_number() over (order by name), cast('31 Dec 2013' as datetime)) as dt
from
sys.columns a
) as dates
WHERE
datepart(dw, dates.dt) = 2
AND dt >= '01 Jan 2014' AND dt < '01 Jan 2015'
AND dt >= '01 Jan 2014' AND dt < '01 Jan 2015'
```

SqlFiddle example for this:

http://sqlfiddle.com/#!6/d41d8/21764

(note that only a few rows are returned because sys.columns is quite small on the SqlFiddle server, try a different system table if this is a problem)

source to share

You can use a suitable number table as a basis for generating the range, for example the master..spt_values table:

```
;WITH dates AS (
SELECT DATEADD(DAY,number,CAST('2014-01-01' AS DATE)) d
FROM master..spt_values WHERE TYPE = 'p'
AND number < 366
)
SELECT
Week = DATEPART(WEEK, d),
DayOfWeek = DATENAME(dw, d),
Date = d
FROM dates
WHERE DATENAME(dw, d) IN ('Monday', 'Friday')
-- or use datepart instead as datename might be specific to language
-- WHERE DATEPART(dw, d) IN (2,6)
```

Output example:

```
Week DayOfWeek Date
----------- ------------------------------ ----------
1 Friday 2014-01-03
2 Monday 2014-01-06
2 Friday 2014-01-10
3 Monday 2014-01-13
3 Friday 2014-01-17
4 Monday 2014-01-20
4 Friday 2014-01-24
5 Monday 2014-01-27
5 Friday 2014-01-31
```

source to share