如何选择一年中的每个星期一日期和每个星期五日期 [英] 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.

例如，这周即将到来，我想要 9/29/2014 和 10/3/2014，但我想要一年中的每个星期.

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.

推荐答案

这里有一个方法(你可能需要检查一周中的哪一天设置为第一天，这里我将星期天作为一周的第一天)

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)

您可以使用具有多行(超过 365 行)的表来CROSS JOIN 以获得一系列日期(计数表).

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

我的 sys 列有 800 多行，你可以使用任何其他表，甚至 CROSS JOIN 一个表到自身上以增加行数

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

这里我使用 row_number 函数来获取行的运行计数，并将每行的日期增加 1 天:

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

现在有了日期的结果集，使用 datepart()

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'

这是一个 SqlFiddle 示例

Here's an example SqlFiddle

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

编辑 2:

如果你想让它们在同一行，一周中的几天至少是不变的，你知道星期五总是在星期一之后 4 天，所以做同样的但只寻找星期一，然后只在星期一增加 4 天..

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'

示例 SqlFiddle:

Example SqlFiddle for this:

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

(注意只有几行返回，因为 SqlFiddle 服务器上的 sys.columns 非常小，如果这是一个问题，请尝试另一个系统表)

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

这篇关于如何选择一年中的每个星期一日期和每个星期五日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！