sqlserver datediff格式转换

SQL Server 时间差函数

最新推荐文章于 2025-01-21 17:19:17 发布

ora600

最新推荐文章于 2025-01-21 17:19:17 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： sqlserver2005

本文链接：https://blog.youkuaiyun.com/heizistudio/article/details/8501568

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