高分!我写的这个函数怎么了,大家看看毛病
实现目的是:输入秒转换为时间
比如: 输入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 '