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

Question

我有 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;

这是我的代码，所以有人请建议我单独获取工作日列表。在线资源有代码可以查找天数，但不能列出所有日期，这就是我的困惑。

Answer 1

试试这个:

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