首页 新闻 搜索 专区 学院

MYSQL 两日期之间的工作日(除去周六日,不考虑节假日)

0
[已解决问题] 解决于 2015-12-02 12:57

求解?

如2015-11-20到2015-11-23得到的日期差为1

小小高的主页 小小高 | 小虾三级 | 园豆:1037
提问于:2015-11-20 17:28
< >
分享
最佳答案
0
Create proc proc_GetWorkDay
@start datetime,@end datetime
as
declare @weekday char(6)
set @start=convert(datetime,convert(varchar(50),@start,23))
set @end=convert(datetime,convert(varchar(50),@end,23))
set nocount on
create table #tp ([id] int identity(1,1),[date] varchar(50),[weekday] char(6))

while @start<=@end
begin
select @weekday=datename(weekday,@start)
if @weekday NOT IN ( 'Saturd' ,'Sunday','星期六','星期日') insert #tp([date],[weekday]) values(convert(varchar(50),@start,23),@weekday)
select @start=dateadd(day,1,@start)
end
select * from #tp
drop table #tp
set nocount off
 GO

调用:
exec proc_GetWorkDay '2015-11-20,'2015-11-23'
奖励园豆:5
αυτоmαη | 菜鸟二级 |园豆:221 | 2015-11-20 17:54

求mysql版的啊,太神

小小高 | 园豆:1037 (小虾三级) | 2015-11-20 18:13

@小小高:  select (floor(days/7)*5+days%7  
    -case when 6 between wd and wd+days%7-1 then 1 else 0 end  
    -case when 7 between wd and wd+days%7-1 then 1 else 0 end ) INTO str
from  
(select datediff(e,s) as days,weekday(s)+1 as wd) as a;    
    ELSE

    select (-(floor(days/7)*5+days%7  
    -case when 6 between wd and wd+days%7-1 then 1 else 0 end  
    -case when 7 between wd and wd+days%7-1 then 1 else 0 end)  ) into str
from  
(select datediff(s,e) as days,weekday(e)+1 as wd) as a;    
    end if;
RETURN str;
END
select first_func('2015-11-17','2015-11-20')

小小高 | 园豆:1037 (小虾三级) | 2015-11-20 22:18
其他回答(1)
0

算上2个日期相差的总天数,将总天数放入一个数组,然后判断出为周六和周日的总天数,总天数-周六周日的总天数,就能得到了。

具体代码自行实现。

贫民窟大侠 | 园豆:4276 (老鸟四级) | 2015-11-20 17:45
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册