sqlserver datediff格式转换

最新推荐文章于 2025-11-10 18:32:50 发布

原创最新推荐文章于 2025-11-10 18:32:50 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

sqlserver2005 专栏收录该内容

65 篇文章

订阅专栏

针对SQL Server中无法直接以hh:mm:ss格式展示时间差的问题，本文介绍了一个自定义函数，该函数接受两个varchar类型的日期时间字符串，计算它们之间的差值，并以指定格式返回。

开发同事提出需求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