sqlserver datediff格式转换

来源：互联网发布：淘宝开店培训心得体会编辑：程序博客网时间：2024/06/05 03:50

开发同事提出需求inserttime和audittime是varchar字段类型，计算出audittime与inserttime之间的差值，显示为hh:mm:ss格式，

而sqlserver datediff没有这种格式，特写函数转换了一下。

create function [dbo].[sumtime](@string1 varchar(40),@string2 varchar(40))
returns varchar(4000)
as
begin
declare @str varchar(150)
if len(cast(floor(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))/3600) as varchar(4)))=1
set @str='0'+cast(floor(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))/3600) as varchar(4))
else
set @str=cast(floor(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))/3600) as varchar(4))
if len(cast(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))/60%60 as varchar(4)))=1
set @str=@str+':0'+cast(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))/60%60 as varchar(4))
else
set @str=@str+':'+cast(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))/60%60 as varchar(4))
if len(cast(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))%60 as varchar(4)))=1
set @str=@str+':0'+cast(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))%60 as varchar(4))
else
set @str=@str+':'+cast(datediff(ss,cast(@string1 as datetime),cast(@string2 as datetime))%60 as varchar(4))
return @str
end