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
to get start dates (talling table).
My sys columns have over 800 rows, you can use any other table or even
table for yourself to multiply the number of rows
Here I used a function
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
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'
Here's a SqlFiddle example
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:
(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)
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