[SQL tips] how to convert int-type data to HH: mm: SS time format

When it comes to some time data, it is not a specific date, for example, the length of a video, it is the number of seconds in the int type.
Exist in the database. We want it to be converted to the string form of HH: mm: SS for output display.

The general idea is to calculate the number of hours in the value, subtract this part, calculate the minute, calculate the second, and then splice the several parts.

In SQL, You Can skillfully use the dateadd date function for processing.

 

declare @totoaltime int
declare @tmpdate datetime
declare @hh varchar(2)
declare @mm varchar(2)
declare @ss varchar(2)
declare @datestr varchar(10)
set @totoaltime=101241

select @totoaltime
select @tmpdate=dateadd(second,@totoaltime,'')
select @tmpdate
select @hh=right('00'+cast(datepart(hh,@tmpdate) as varchar),2)
select @mm=right('00'+cast(datepart(n,@tmpdate) as varchar),2)
select @ss=right('00'+cast(datepart(s,@tmpdate) as varchar),2)
select @datestr=@hh+':'+@mm+':'+@ss
select @datestr

Encapsulate it into a custom function, and remove the hour part in less than one hour.

create function [dbo].[secondToDatestr](@second int)
returns  varchar(10) 
as
begin
declare @tmpdate datetime,@rtn varchar(10)
set @tmpdate=dateadd(second,@second,'')
set @rtn=right('00'+cast(datepart(hh,@tmpdate) as varchar),2)+':'
+right('00'+cast(datepart(n,@tmpdate) as varchar),2)+':'
+right('00'+cast(datepart(s,@tmpdate) as varchar),2)
if Charindex('00:',@rtn)=1
set @rtn=Stuff(@rtn,1,3,'')

return @rtn
end