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