How to select every Monday date and every Friday date in the year

Question

basically I want to be able to select monday and friday for every week in the year.

So for example this week coming i want 9/29/2014 and 10/3/2014, but i want this for every week in the year.

Answer 1

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

You can use a table with many rows (more than 365) to CROSS JOIN to in order to get a run of dates (a tally table).

My sys columns has over 800 rows in, you could use any other table or even CROSS JOIN a table onto itself to multiply up the number of rows

Here I used the row_number function to get a running count of rows 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

With the result set of dates now, it's trivial to check the day of 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 an example SqlFiddle

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

Edit 2:

If you want them on the same row, days of the week at least are constant, you know Friday is always 4 days after Monday so do the same but only look for Mondays, then just add 4 days to the 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'

Example SqlFiddle for this:

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

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