declare @secends int -- 数据计算的秒数 select @secends = 360000-- 转换时分秒 select cast(cast(floor(@secends/86400)*24 as int) + cast(left(convert(varchar(8),dateadd(ss,360000,120),108),2) as int) as varchar) + right(convert(varchar(8),dateadd(ss,360000,120),108),6)
DECLARE @intValue INT SET @intValue=360001select cast(datediff(hh,'1990-1-1',dateadd(ss,@intValue,'1990-1-1'))AS VARCHAR(20)) + right(cast(convert(varchar(20),dateadd(ss,@intValue,108),120)AS VARCHAR(20)),6)-------------------------- 100:00:01(1 row(s) affected)
CREATE FUNCTION dbo.FormatSecondsAsHHMMSS( @seconds int ) RETURNS varchar(20) BEGIN DECLARE @hh varchar(10), @mm varchar(10), @ss varchar(10) SET @hh = CAST(@seconds / 3600 AS varchar(10)) IF LEN(@hh) = 1 SET @hh = '0' + @hh SET @seconds = @seconds % 3600 SET @mm = RIGHT('00' + CAST(@seconds / 60 AS varchar(10)), 2) SET @ss = RIGHT('00' + CAST(@seconds % 60 AS varchar(10)), 2) RETURN @hh + ':' + @mm + ':' + @ss END GO SELECT dbo.FormatSecondsAsHHMMSS(1000000)
DECLARE @I INT SELECT @I=36875SELECT @I,LTRIM(@I/3600)+':'+RIGHT((@I%3600)/60,2)+':'+RIGHT(@I%60,2) --36875 10:14:35 是要这东西吗?
--另类的写法 declare @i int set @i=360123select cast(@i/3600 as varchar) +':'+ case when len(@i%3600/60)=1 then '0'+cast(@i%3600/60 as varchar) else cast(@i%60/60 as varchar) end +':'+ case when len(@i%60)=1 then '0'+cast(@i%60 as varchar) else cast(@i%60 as varchar) end/* 100:02:03 */
declare @secends int
-- 数据计算的秒数
select @secends = 360000-- 转换时分秒
select cast(cast(floor(@secends/86400)*24 as int) +
cast(left(convert(varchar(8),dateadd(ss,360000,120),108),2) as int) as varchar) +
right(convert(varchar(8),dateadd(ss,360000,120),108),6)
SET @intValue=360001select cast(datediff(hh,'1990-1-1',dateadd(ss,@intValue,'1990-1-1'))AS VARCHAR(20))
+ right(cast(convert(varchar(20),dateadd(ss,@intValue,108),120)AS VARCHAR(20)),6)--------------------------
100:00:01(1 row(s) affected)
CREATE FUNCTION dbo.FormatSecondsAsHHMMSS(
@seconds int
)
RETURNS varchar(20)
BEGIN
DECLARE @hh varchar(10), @mm varchar(10), @ss varchar(10)
SET @hh = CAST(@seconds / 3600 AS varchar(10))
IF LEN(@hh) = 1 SET @hh = '0' + @hh
SET @seconds = @seconds % 3600
SET @mm = RIGHT('00' + CAST(@seconds / 60 AS varchar(10)), 2)
SET @ss = RIGHT('00' + CAST(@seconds % 60 AS varchar(10)), 2)
RETURN @hh + ':' + @mm + ':' + @ss
END
GO
SELECT dbo.FormatSecondsAsHHMMSS(1000000)
SELECT @I=36875SELECT @I,LTRIM(@I/3600)+':'+RIGHT((@I%3600)/60,2)+':'+RIGHT(@I%60,2)
--36875 10:14:35
是要这东西吗?
--另类的写法
declare @i int
set @i=360123select cast(@i/3600 as varchar)
+':'+
case when len(@i%3600/60)=1 then '0'+cast(@i%3600/60 as varchar) else cast(@i%60/60 as varchar) end
+':'+
case when len(@i%60)=1 then '0'+cast(@i%60 as varchar) else cast(@i%60 as varchar) end/*
100:02:03
*/