首页 新闻 会员 周边

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

0
悬赏园豆：5 [待解决问题]


--节假日表
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。
求解

知识青年下乡去的主页 知识青年下乡去 | 初学一级 | 园豆：6
提问于：2016-08-05 16:58
< >
分享
所有回答(3)
0

伪代码如下：

1、给定日期CurrentDate

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

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

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

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

6、Result=iMonthday-iWeekendcount-iHolidayCount

好了，最难的都解决了，其他的你看书吧。

爱编程的大叔 | 园豆：30839 (高人七级) | 2016-08-05 17:12
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)

set @firstday=dateadd(day,1,@firstday)

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

 

悦光阴 | 园豆：2251 (老鸟四级) | 2016-08-20 17:51

不过大哥，20160807 是sunday，你说的应该是20160804吧

支持(0) 反对(0) 悦光阴 | 园豆：2251 (老鸟四级) | 2016-08-20 17:53
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 (
    SELECT RW,DT=DATEADD(DAY,RW-DAY(@CAL),@CAL)
    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
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。