在SQL中列出两个日期之间的所有工作日

7

我有一段SQL代码,可以生成两个日期之间的日期列表,但我想要生成给定两个日期之间的工作日(周内日)。

DECLARE @MinDate DATE = '20140101', @MaxDate DATE = '20140106';
SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
这是我的代码,请有经验的人建议我如何获取星期几列表。 在线资源提供了查找天数而非列出所有日期的代码,这让我感到困惑。

在这里已经提出了一种好的方法来找到工作日。[https://dev59.com/2XVC5IYBdhLWcg3wjx_u] - Sim1
每周减去两天? - jarlh
4个回答

9

试试这个:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106'

;WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
N4 (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N3 AS N1 CROSS JOIN N3 AS N2)
SELECT  Date = DATEADD(DAY, N - 1, @MinDate)
FROM    N4
WHERE 
  N < DATEDIFF(DAY, @MinDate, @MaxDate) + 2 AND
  DATEDIFF(DAY, 1 - N, @MinDate) % 7 NOT IN (5,6)

结果:

Date
2014-01-01
2014-01-02
2014-01-03
2014-01-06

1
DATEDIFF(d,1 - N, @MinDate) % 7 NOT IN (5,6) 中使用模数7是可靠的,不受SQL Server设置的影响,也不依赖于语言。 - Paul Maxwell
@Used_By_Already:使此查询与设置无关的不是取模7,而是将 int 转换为 datetime 的隐式转换。然而,问题是这个解决方案存在一个错误。如果您尝试将 @MinDate 更改为 20140102,则会得到不正确的结果。也许 t-clausen.dk 指的是 DATEDIFF 的这个版本:DATEDIFF(DAY,0,DATEADD(DAY,N-1,@MinDate))%7 NOT IN(5,6) - Andriy M
我所指的并不是设置独立性的原因,而是“取模7”的使用。我试图描述“% 7”的作用并提供其优点(也许表述不够清晰),即(5,6)的结果是与设置无关的。你对于需要dateadd()的观点可能是正确的,我会让作者回复这个问题。干杯! - Paul Maxwell

6
将原始查询作为子查询,生成两个给定日期之间的所有日期,然后在外部查询中进行过滤。
SET DATEFIRST 1

select [Date] from
(
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@MinDate)
FROM    sys.all_objects a
    CROSS JOIN sys.all_objects b
)
where datename(dw,[Date]) not in ('Saturday','Sunday')

除了某些原因,我的SQL浏览器无法将表格设置为表格,其他都完美无缺。 - CashCow
在您的代码中,设置DateFirst对DateDiff、DateAdd或DateName函数没有影响。该行可以被删除而不会产生任何影响。此外,您需要为()中的隐含表指定别名,否则它将无法在SQL Server中运行(例如... Cross Join sys.all_objects b)MyTemp where datename(...)。 - Jeff Winchell

3
DECLARE @MinDate DATE = '20140101', @MaxDate DATE = '20140106';
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
WHERE datename(dw,@MinDate) NOT IN ('Saturday','Sunday') 
      AND datename(dw,@MaxDate) NOT IN ('Saturday','Sunday') ;

谢谢你的回复,马特。但是周末(星期六和星期天)没有被过滤掉。我不想在我的代码中包含周末日期。 - user1799596
当比较星期六和星期日的日期部分时,不可靠的是使用日期部分检查,它取决于datefirst的设置,而默认值可能因数据库而异。 - t-clausen.dk
是的,我也觉得可能是这样,我会删除第二个版本。 - Matt
1
我仍然没有得到正确的输出,例如,如果我将这些值作为开始日期和结束日期传递'2015/12/13','2015/12/27',则不会生成任何日期。 - user1799596
因为你的开始日期和结束日期有周末日期,如果你使用它们,它们不会起作用,因为它们没有包括在内,请尝试使用“2015/12/14”和“2015/12/25”。 - Matt

2

很久以前,我建立了一个日历表来回答像你这样的问题。除了易于使用之外,其主要好处是您可以查看针对日历表的查询并说:“那显然是正确的。”

select cal_date, day_of_week
from calendar
where day_of_week in ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
  and cal_date between '2014-01-01' and '2014-01-06'
order by cal_date;
cal_date    day_of_week
--
2014-01-01  周三
2014-01-02  周四
2014-01-03  周五
2014-01-06  周一

我还有一个星期几的视图,所以我本可以查询它而不是原表。

select cal_date, day_of_week
from weekdays
where cal_date between '2014-01-01' and '2014-01-06'
order by cal_date;

3
如何创建日历表? - user1799596
以下是使用PostgreSQL的日历表示例(https://dev59.com/elTTa4cB1Zd3GeqPsGuf#5030686)。适应SQL Server应该很容易,特别是因为你只需要这两列。 - Mike Sherrill 'Cat Recall'

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,