# sql：当前日期是本月第几个工作日。

0

--节假日表
create table #Holiday(dateday datetime)

insert into #Holiday values('2016-08-03 15:10:23');
insert into #Holiday values('2016-08-05 15:10:23');
insert into #Holiday values('2016-08-11 15:10:23');
insert into #Holiday values('2016-08-15 15:10:23');

--需求：算出当前日期是本月第几个工作日、并排除以上表的日期。
--例：日期：2016-08-07 15:12:41 排除8-3、8-5.再排除周末  最终工作日输出：3。

0

1、给定日期CurrentDate

2、求出给定日期当前月第一天FirstDate

3、计算FirstDate~CurrentDate之间有多少个假日iHolidayCount（最好能有周末标识，排除周末）

4、计算FirstDate~CurrentDate之间有多个周末iWeekendCount

5、计算CurrentDate是本月第几天iMonthDay

6、Result=iMonthday-iWeekendcount-iHolidayCount

0

column的datatime 使用 date 较好，我居然慢慢写出来了

```create table #Holiday(dateday date)

insert into #Holiday values('2016-08-03');
insert into #Holiday values('2016-08-05');
insert into #Holiday values('2016-08-11');
insert into #Holiday values('2016-08-15');

declare @DateNow date
set @DateNow=getdate()

declare @firstday date
declare @endday date
set @firstday=datefromparts(year(@datenow),month(@DateNow),1)
set @endday=eomonth(@datenow)

create table #Date(dateday date,IsWeedEnd bit)

--select @@datefirst  :default 7
set datefirst 1

while @firstday<=@endday
begin

declare @IsWeedEnd bit
set @IsWeedEnd=iif(datepart(weekday,@firstday) in(6,7),1,0)
insert into #Date
values(@firstday,@IsWeedEnd)

end

if datepart(weekday,@DateNow) in(6,7)
begin
select 'weekend'
end
else
begin
select p.dateday,p.dayno
from(
select d.dateday,row_number() over(order by d.dateday) as dayno
from #Date d
left join #Holiday h
on d.dateday=h.dateday
where h.dateday is null
and d.IsWeedEnd=0
) as p
where p.dateday=@DateNow
end
--set default 7
set datefirst 7```

0
```create table #Holiday(dateday datetime)

insert into #Holiday values('2016-08-03 15:10:23');
insert into #Holiday values('2016-08-05 15:10:23');
insert into #Holiday values('2016-08-11 15:10:23');
insert into #Holiday values('2016-08-15 15:10:23');

SET DATEFIRST 1

DECLARE @CAL DATETIME --需要计算的日期
SET @CAL ='2016-08-07 15:12:41'

;WITH CTE
AS (
SELECT RW=ROW_NUMBER()OVER(ORDER BY name)
FROM [Master].dbo.spt_values      --找一个系统表(记录数超过31的表)
)
,CTE2
AS (
FROM CTE
WHERE RW <=DAY(@CAL)
)
,CTE3
AS (
SELECT A.RW,A.DT
,IsHoliday =Case When DATEPART(weekday ,DT) IN(6,7) OR B.dateday is not null THEN 1 ELSE 0 END
FROM CTE2 A
LEFT JOIN #Holiday B ON CONVERT(NVARCHAR(10),A.DT,121) =CONVERT(NVARCHAR(10),B.dateday,121)
)
SELECT Count(1) FROM CTE3
WHERE IsHoliday =0```

Shannon | 园豆：611 (小虾三级) | 2016-09-06 09:48

您需要登录以后才能回答，未注册用户请先注册