SQL Server获取2022年工作日列表（含调休上班）
1. 创建节假日表
create table holiday(holiday_date date,
--日期（仅含节假日和调休上班）
flag char(1)
--0-调休上班 1-节假日
)
2. 插入节假日数据（含调休上班）
insert into holiday values('2022-01-01', '1');insert into holiday values('2022-01-02', '1');
insert into holiday values('2022-01-03', '1');
insert into holiday values('2022-01-29', '0');
insert into holiday values('2022-01-30', '0');
insert into holiday values('2022-01-31', '1');
insert into holiday values('2022-02-01', '1');
insert into holiday values('2022-02-02', '1');
insert into holiday values('2022-02-03', '1');
insert into holiday values('2022-02-04', '1');
insert into holiday values('2022-02-05', '1');
insert into holiday values('2022-02-06', '1');
insert into holiday values('2022-04-02', '0');
insert into holiday values('2022-04-03', '1');
insert into holiday values('2022-04-04', '1');
insert into holiday values('2022-04-05', '1');
insert into holiday values('2022-04-24', '0');
insert into holiday values('2022-04-30', '1');
insert into holiday values('2022-05-01', '1');
insert into holiday values('2022-05-02', '1');
insert into holiday values('2022-05-03', '1');
insert into holiday values('2022-05-04', '1');
insert into holiday values('2022-05-07', '0');
insert into holiday values('2022-06-03', '1');
insert into holiday values('2022-06-04', '1');
insert into holiday values('2022-06-05', '1');
insert into holiday values('2022-09-10', '1');
insert into holiday values('2022-09-11', '1');
insert into holiday values('2022-09-12', '1');
insert into holiday values('2022-10-01', '1');
insert into holiday values('2022-10-02', '1');
insert into holiday values('2022-10-03', '1');
insert into holiday values('2022-10-04', '1');
insert into holiday values('2022-10-05', '1');
insert into holiday values('2022-10-06', '1');
insert into holiday values('2022-10-07', '1');
insert into holiday values('2022-10-08', '0');
insert into holiday values('2022-10-09', '0');
3. 获取工作日列表
declare @date_begin date = '2022-01-01'declare @date_end date = '2022-12-31'
select convert(varchar(20), dateadd(day, n.number, @date_begin), 23) work_date_list
from master..spt_values n
left join holiday h on h.holiday_date = dateadd(day, n.number, @date_begin)
where n.type = 'p'
and n.number <= datediff(day, @date_begin, @date_end)
and (h.flag = '0' or (datepart(dw, dateadd(day, n.number, @date_begin)) not in (1, 7) and h.holiday_date is null))