SQL Server 返回两个时间的小时部分之差

yghuaa 2013-12-20 02:40:49

create function gethours(@stime datetime,@etime datetime)
returns int
as
begin
declare @hstime nvarchar(30)
declare @hetime nvarchar(30)
select @hstime=Ltrim(datepart(hh,@stime))
select @hetime=Ltrim(datepart(hh,@etime))
if @hstime<8 and @hetime<17 and @hetime>8
return @hetime-8
else if @hstime<8 and @hetime>17
return 8
else if @hstime<8 and @hetime<8
return 0
else if @hstime>17 and @hetime>17
return 0
else if @hstime>17 and @hetime<17 and @hetime>8
return @hetime-8
else if @hstime>17 and @hetime<8
return 0
else
return @hetime-@hstime
end

提示信息：
消息 455，级别 16，状态 2，过程 gethours，第 22 行
The last statement included within a function must be a return statement.
不知道怎么解决啊

...全文

302 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

LongRui888 2013-12-23

打赏
举报

引用 7 楼 ITX2000 的回复:

[quote=引用 3 楼 yupeigu 的回复:] 如果你是由什么特殊的需求，我把你的函数改了一下，应该不会报错了：

 create function gethours(@stime datetime,@etime datetime)
  returns int
  as
  
begin
  
  declare @hstime nvarchar(30)
  declare @hetime nvarchar(30)
  declare @result int
  
  select @hstime=Ltrim(datepart(hh,@stime))
  select @hetime=Ltrim(datepart(hh,@etime))
  
  if @hstime<8 and @hetime<17 and @hetime>8
  set @result=@hetime-8
  
  else if @hstime<8 and @hetime>17
  set @result=8
  
  else if @hstime<8 and @hetime<8
  set @result=0
  
  else if @hstime>17 and @hetime>17
  set @result=0
  
  else if @hstime>17 and @hetime<17 and @hetime>8
  set @result=@hetime-8
   
  else if @hstime>17 and @hetime<8
  set @result=0
  
 else 
    set @result=(cast(@hetime as int)- cast(@hstime as int))


return @result

end
go

我执行之后发现，只会返回最后else后面的值 set @result=(cast(@hetime as int)- cast(@hstime as int)) 满足else if条件的也没返回其值啊。这是怎么回事？？[/quote] 改成这样试试：


 create function gethours(@stime datetime,@etime datetime)
  returns int
  as
  
begin
  
  declare @hstime int
  declare @hetime int
  declare @result int
  
  select @hstime=datepart(hh,@stime)
  select @hetime=datepart(hh,@etime)
  
  if @hstime<8 and @hetime<17 and @hetime>8
  set @result=@hetime-8
  
  else if @hstime<8 and @hetime>17
  set @result=8
  
  else if @hstime<8 and @hetime<8
  set @result=0
  
  else if @hstime>17 and @hetime>17
  set @result=0
  
  else if @hstime>17 and @hetime<17 and @hetime>8
  set @result=@hetime-8
   
  else if @hstime>17 and @hetime<8
  set @result=0
  
 else 
    set @result=(cast(@hetime as int)- cast(@hstime as int))


return @result

end
go

yghuaa 2013-12-23

打赏
举报

引用 3 楼 yupeigu 的回复:

如果你是由什么特殊的需求，我把你的函数改了一下，应该不会报错了：

 create function gethours(@stime datetime,@etime datetime)
  returns int
  as
  
begin
  
  declare @hstime nvarchar(30)
  declare @hetime nvarchar(30)
  declare @result int
  
  select @hstime=Ltrim(datepart(hh,@stime))
  select @hetime=Ltrim(datepart(hh,@etime))
  
  if @hstime<8 and @hetime<17 and @hetime>8
  set @result=@hetime-8
  
  else if @hstime<8 and @hetime>17
  set @result=8
  
  else if @hstime<8 and @hetime<8
  set @result=0
  
  else if @hstime>17 and @hetime>17
  set @result=0
  
  else if @hstime>17 and @hetime<17 and @hetime>8
  set @result=@hetime-8
   
  else if @hstime>17 and @hetime<8
  set @result=0
  
 else 
    set @result=(cast(@hetime as int)- cast(@hstime as int))


return @result

end
go

我执行之后发现，只会返回最后else后面的值 set @result=(cast(@hetime as int)- cast(@hstime as int)) 满足else if条件的也没返回其值啊。这是怎么回事？？

發糞塗牆 2013-12-20

打赏
举报

加case when 啊

yghuaa 2013-12-20

打赏
举报

引用 1 楼 DBA_Huangzj 的回复:

select datediff(hh,@stime ,@etime)这样不行吗？

不行啊，只计算8点到17点的时间差

zbdzjx 2013-12-20

打赏
举报

计算上班时间的算法？？？？

LongRui888 2013-12-20

打赏
举报

如果你是由什么特殊的需求，我把你的函数改了一下，应该不会报错了：

 create function gethours(@stime datetime,@etime datetime)
  returns int
  as
  
begin
  
  declare @hstime nvarchar(30)
  declare @hetime nvarchar(30)
  declare @result int
  
  select @hstime=Ltrim(datepart(hh,@stime))
  select @hetime=Ltrim(datepart(hh,@etime))
  
  if @hstime<8 and @hetime<17 and @hetime>8
  set @result=@hetime-8
  
  else if @hstime<8 and @hetime>17
  set @result=8
  
  else if @hstime<8 and @hetime<8
  set @result=0
  
  else if @hstime>17 and @hetime>17
  set @result=0
  
  else if @hstime>17 and @hetime<17 and @hetime>8
  set @result=@hetime-8
   
  else if @hstime>17 and @hetime<8
  set @result=0
  
 else 
    set @result=(cast(@hetime as int)- cast(@hstime as int))


return @result

end
go

LongRui888 2013-12-20