sqlserver datediff格式转换

?

开发同事提出需求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(@string2 as datetime))/3600) as varchar(4)) else set @str=cast(floor(datediff(ss,cast(@string2 as datetime))/3600) as varchar(4)) if len(cast(datediff(ss,cast(@string2 as datetime))/60%60 as varchar(4)))=1 set @str=@str+':0'+cast(datediff(ss,cast(@string2 as datetime))/60%60 as varchar(4)) else set @str=@str+':'+cast(datediff(ss,cast(@string2 as datetime))/60%60 as varchar(4)) if len(cast(datediff(ss,cast(@string2 as datetime))%60 as varchar(4)))=1 set @str=@str+':0'+cast(datediff(ss,cast(@string2 as datetime))%60 as varchar(4)) else set @str=@str+':'+cast(datediff(ss,cast(@string2 as datetime))%60 as varchar(4)) return @str end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!