需求 算出两个日期中的工作天数 (就是两个日期除去周六日的天数)
以下是我写的存储过程 有问题 dateAdd函数报错
可能不止这一处错误 以下是代码create procedure sp_testTime
@startTime datetime, @endTime datetimeasdeclare @numDays int,@sTime datetime
set @numDays =0
set @sTime=@endTimewhile(datediff(day,@sTime,@endTime)=0)
begin
if(datepart(weekday,@sTime)<>1 and datepart(weekday,@sTime)<>7)
begin
set @numDays =@numDays+1
end DateAdd(D,1,@sTime)
end select @numDays
以下是我写的存储过程 有问题 dateAdd函数报错
可能不止这一处错误 以下是代码create procedure sp_testTime
@startTime datetime, @endTime datetimeasdeclare @numDays int,@sTime datetime
set @numDays =0
set @sTime=@endTimewhile(datediff(day,@sTime,@endTime)=0)
begin
if(datepart(weekday,@sTime)<>1 and datepart(weekday,@sTime)<>7)
begin
set @numDays =@numDays+1
end DateAdd(D,1,@sTime)
end select @numDays
不知道楼主是不是这个意思
--计算个日期之间的工作日(周末双休,不考虑特殊节日)create function workday (@beg_time datetime,@end_time datetime)returns intasbegin declare @n intset @n=(select 工作日=days/7*5+days%7 -case when 6 between wd and wd+days%7-1 then 1 end -case when 7 between wd and wd+days%7-1 then 1 endfrom(select datediff(day,@beg_time,@end_time)+1 as days, datepart(weekday,@beg_time+@@datefirst-1) as wd) k)return @Nendgodeclare @beg_time datetime,@end_time datetimeset @beg_time='2010-01-01' set @end_time='2010-06-28'--执行函数select 工作日=dbo.workday(@beg_time,@end_time)/*工作日-----------127
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/feixianxxx/archive/2009/10/15/4677124.aspx
改了 没错误了 但还是有错误 测试结果是0
create procedure sp_testTime
@startTime datetime, @endTime datetime
asdeclare @numDays int,@sTime datetime
set @numDays =0
set @sTime=@endTimewhile(datediff(day,@sTime,@endTime)=0)
begin
if(datepart(weekday,@sTime)<>1 and datepart(weekday,@sTime)<>7)
begin
set @numDays =@numDays+1
end set @sTime=DateAdd(D,1,@sTime)
end select @numDays--测试exec sp_testTime '2004-01-01' , '2004-02-01'
@startTime datetime, @endTime datetimeasdeclare @numDays int,@sTime datetime
set @numDays =0
set @sTime=@startTimewhile(datediff(day,@sTime,@endTime)=0)
begin
if(datepart(weekday,@sTime)<>1 and datepart(weekday,@sTime)<>7)
begin
set @numDays =@numDays+1
end set @sTime=dateadd(D,1,@sTime)
end
select @numDays
谢谢 各位的指点 create procedure sp_testTime
@startTime datetime, @endTime datetimeasdeclare @numDays int,@sTime datetime
set @numDays =1
set @sTime=@startTimewhile(datediff(day,@sTime,@endTime)<>0)
begin
if(datepart(weekday,@sTime)<>1 and datepart(weekday,@sTime)<>7)
begin
set @numDays =@numDays+1
end set @sTime=DateAdd(D,1,@sTime)
end select @numDays--测试
exec sp_testTime '2004-01-01' , '2004-02-01'
@startTime datetime, @endTime datetime
as
begin
declare @numDays int
set @numDays =0while(@startTime<=@endTime)
begin
if(datepart(weekday,@startTime)<>1 and datepart(weekday,@startTime)<>7)
begin
set @numDays =@numDays+1
end set @startTime=DateAdd(Dd,1,@startTime)
end select @numDays
end
--测试exec testTime '2004-01-01' , '2004-02-01'
-----------
22(1 行受影响)