CODEWITHSUNDEEP
sqlsql-server-2012
user222427
user222427

Reputation:

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

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.

Upvotes: 6

Views: 14927

Answers (3)

Nickname_used
Nickname_used

Reputation: 428

SELECT extract(isodow from "date_source") as datee 1-monday 7-sunday

and then in WHERE write datee IN ('1','2'....)

Upvotes: 0

Charleh
Charleh

Reputation: 14002

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)

Upvotes: 3

jpw
jpw

Reputation: 44911

You can use a suitable table with numbers, like the master..spt_values table as basis for the range generation:

;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)

Sample output:

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

Upvotes: 3

Related Questions