高分！我写的这个函数怎么了，大家看看毛病
实现目的是:输入秒转换为时间
比如： 输入20 转换为00:00:20,输入100转换为:00:01:40
代码如下，但是出问题哦，输入小于60的值就成空了
SELECT dbo.sp_NumberToDate1(200) 结果是对的 00:03:20
但是把200换成20结果就为null
-------------------------
CREATE function sp_NumberToDate(@sNumber int)
returns varchar(100)
as
begin
declare @ss int,@i int,@j int,@sString varchar(100)
set @ss=@sNumber
if (@ss <3600)
begin
if (@ss <60)
--set @i=FLOOR(@ss)
set @sString= '00: '+ '00: '+cast((@ss-0) as varchar(100))
else
set @i=FLOOR(@ss/60)
if(@i <10)
if((@ss-@i*60) <10)
set @sString= '00:0 '+cast(@i as varchar(100))+ ':0 '+cast((@ss-@i*60) as varchar(100))
else
set @sString= '00:0 '+cast(@i as varchar(100))+ ': '+cast((@ss-@i*60) as varchar(100))
else
if((@ss-@i*60) <10)
set @sString= '00: '+cast(@i as varchar(100))+ ':0 '+cast((@ss-@i*60) as varchar(100))
else
set @sString= '00: '+cast(@i as varchar(100))+ ': '+cast((@ss-@i*60) as varchar(100))
end
else
begin
set @j=FLOOR(@ss/3600)
set @i=FLOOR((@ss-@j*3600)/60)
if(@j <10)
if(@i <10)
if((@ss-@i*60) <10)
set @sString= '0 '+cast(@j as varchar(100))+ ':0 '+cast(@i as varchar(100))+ ':0 '+cast((@ss-@j*3600-@i*60) as varchar(100))
else
set @sString= '0 '+cast(@j as varchar(100))+ ':0 '+cast(@i as varchar(100))+ ': '+cast((@ss-@j*3600-@i*60) as varchar(100))
else
if((@ss-@i*60) <10)
set @sString= '0 '+cast(@j as varchar(100))+ ': '+cast(@i as varchar(100))+ ':0 '+cast((@ss-@j*3600-@i*60) as varchar(100))
else
set @sString= '0 '+cast(@j as varchar(100))+ ': '+cast(@i as varchar(100))+ ': '+cast((@ss-@j*3600-@i*60) as varchar(100))
else
if(@i <10)
if((@ss-@i*60) <10)
set @sString=cast(@j as varchar(100))+ ':0 '+cast(@i as varchar(100))+ ':0 '+cast((@ss-@j*3600-@i*60) as varchar(100))
else
set @sString=cast(@j as varchar(100))+ ':0 '+cast(@i as varchar(100))+ ': '+cast((@ss-@j*3600-@i*60) as varchar(100))
else
if((@ss-@i*60) <10)
set @sString=cast(@j as varchar(100))+ ': '+cast(@i as varchar(100))+ ':0 '+cast((@ss-@j*3600-@i*60) as varchar(100))
else
set @sString=cast(@j as varchar(100))+ ': '+cast(@i as varchar(100))+ ': '+cast((@ss-@j*3600-@i*60) as varchar(100))
end
return(@sString)
end
[解决办法]
改寫下
Create Function sp_NumberToDate1(@sNumber Int)
Returns Varchar(100)
As
Begin
Return Right(100 + @sNumber / 3600, 2) + ': ' + Right(100 + (@sNumber - @sNumber / 3600 * 3600) / 60, 2) + ': ' + Right(100 + @sNumber % 60, 2)
End
GO
SELECT dbo.sp_NumberToDate1(200)
SELECT dbo.sp_NumberToDate1(20)
GO
Drop Function sp_NumberToDate1
--Result
/*
00:03:20
00:00:20
*/
[解决办法]
declare @sNumber int
set @sNumber=60
select convert(varchar(10),dateadd(second,@sNumber,cast( '2007/1/1 ' as datetime)),8)
[解决办法]
對阿,只要將 00:00:00 + 輸入的數字秒數,就是結果了吧
[解决办法]
美女的方法很帅
