if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_workday]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_workday]
GO/*--得到指定日期之间的工作日天数 2004.12 修正了计算不准确的问题
增加了处理,避免处理过程受 SET DATEFIRST 设置的影响
--邹建 2004.06(引用请保留此信息)--*//*--调用示例 select dbo.f_workday('2004-06-20','2004-06-27')
select dbo.f_workday('2004-12-1','2004-12-31')
select dbo.f_workday('2004-12-1','2005-1-1')
select dbo.f_workday('2004-12-1','2005-1-2')
select dbo.f_workday('2004-12-1','2005-1-3')
--*/
create function f_workday(
@dt_begin datetime,
@dt_end datetime
)returns int
as
begin
declare @dt datetime,@re int,@i int
if @dt_begin>@dt_end
select @dt=@dt_begin
,@dt_begin=@dt_end
,@dt_end=@dt
select @i=datediff(day,@dt_begin,@dt_end)+1
,@re=@i/7*5
,@dt=dateadd(day,@i/7*7,@dt_begin)
while @dt<=@dt_end
begin
select @re=case
when (@@datefirst+datepart(weekday,@dt)-1)%7 in(0,6)
then @re else @re+1 end
,@dt=@dt+1
end
return(@re)
end
go
drop function [dbo].[f_workday]
GO/*--得到指定日期之间的工作日天数 2004.12 修正了计算不准确的问题
增加了处理,避免处理过程受 SET DATEFIRST 设置的影响
--邹建 2004.06(引用请保留此信息)--*//*--调用示例 select dbo.f_workday('2004-06-20','2004-06-27')
select dbo.f_workday('2004-12-1','2004-12-31')
select dbo.f_workday('2004-12-1','2005-1-1')
select dbo.f_workday('2004-12-1','2005-1-2')
select dbo.f_workday('2004-12-1','2005-1-3')
--*/
create function f_workday(
@dt_begin datetime,
@dt_end datetime
)returns int
as
begin
declare @dt datetime,@re int,@i int
if @dt_begin>@dt_end
select @dt=@dt_begin
,@dt_begin=@dt_end
,@dt_end=@dt
select @i=datediff(day,@dt_begin,@dt_end)+1
,@re=@i/7*5
,@dt=dateadd(day,@i/7*7,@dt_begin)
while @dt<=@dt_end
begin
select @re=case
when (@@datefirst+datepart(weekday,@dt)-1)%7 in(0,6)
then @re else @re+1 end
,@dt=@dt+1
end
return(@re)
end
go
Create function f_workday(@dt_begin datetime,@dt_end datetime)
returns intbegin
--Dim dt,re,i
declare @dt datetime
declare @re int
declare @i int if @dt_begin>@dt_end
begin
set @dt=@dt_begin
set @dt_begin=@dt_end
set @dt_end=@dt end
set @dt=@dt_begin
set @i=0
while datediff(dd,@dt,@dt_end)>0
begin
if datepart(DW,@dt)<>1 And datepart(DW,@dt)<>7
set @i=@i+1
set @dt=dateadd(DD,1,@dt)
end
return(@i)
end
select dbo.f_workday('2005/01/16','2005/01/26')
as
begin
declare @i int
set @i = 0
while @fromdate<=@todate
begin
if datepart(Dw,@fromdate)<>1 and datepart(Dw,@fromdate)<>7
set @i=@i+1
set @fromdate = dateadd(day,1,@fromdate)
end
return(@i)
end
go
同时返回告知测试结果:邹建和dellair的代码计算正确,孔腎的代码和我的一样,在跨星期计算时有误。
再次感谢上面几位!!!