日期:2014-05-17  浏览次数:20604 次

sql怎么把某一列的秒全部换算成小时?
就是我有一列全部是以秒为单位的,怎么把它换算成000:00:00格式?

------解决方案--------------------
SQL code


 CREATE TABLE #t(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Time] datetime NULL  
     
)
INSERT INTO #t
SELECT 59 UNION ALL
SELECT 60 UNION ALL
SELECT 61 UNION ALL
SELECT 119 UNION ALL
SELECT 120 UNION ALL
SELECT 121 UNION ALL
SELECT 3599 UNION ALL
SELECT 3600 UNION ALL
SELECT 3601 UNION ALL
SELECT 86399

 

 
select ID,case when cast([Time] as int) between 0 and 24*60*60-1 then CONVERT(varchar(100), cast(floor(cast([Time] as int)/3600)as varchar(100))
+':'+cast(floor((cast([Time] as int)%3600)/60)as varchar(100))+':'+cast((cast([Time] as int)%60) as varchar(100)),108) end N'Time'
 from #t
drop table #t


/*
结果


ID    Time
1    0:0:59
2    0:1:0
3    0:1:1
4    0:1:59
5    0:2:0
6    0:2:1
7    0:59:59
8    1:0:0
9    1:0:1
10    23:59:59

--------------------------
(10 行受影响)

(10 行受影响)

*/

------解决方案--------------------
[code=SQL][/code]



SELECT CASE len(CAST(12305321/3600 AS VARCHAR))
WHEN 0 THEN '000'
WHEN 1 THEN '00' + CAST(12305321/3600 AS VARCHAR)
WHEN 2 THEN '0' + CAST(12305321/3600 AS VARCHAR)
ELSE CAST(12305321/3600 AS VARCHAR)
END + ':' +

CASE len(CAST(12305321%60 AS VARCHAR))
WHEN 1 THEN '0' + CAST(12305321%60 AS VARCHAR)
WHEN 2 THEN CAST(12305321%60 AS VARCHAR)
ELSE '00'
END + ':' + 

CASE len(CAST(12305321/60%60 AS VARCHAR))
WHEN 1 THEN '0' + CAST(12305321/60%60 AS VARCHAR)
WHEN 2 THEN CAST(12305321/60%60 AS VARCHAR)
ELSE '00'
END



------解决方案--------------------
SQL code
DECLARE @a BIGINT=1289932
SELECT CONVERT(VARCHAR(10),@a/3600)+'时'+CONVERT(VARCHAR(10),@a%3600/60)+'分'+CONVERT(VARCHAR(10),@a%3600/60/60)+'秒'
/*

------------------------------------
358时18分0秒

(1 行受影响)


*/

------解决方案--------------------
SQL code
CREATE TABLE #t(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Time] int NULL  
     
)
INSERT INTO #t
SELECT 59 UNION ALL
SELECT 60 UNION ALL
SELECT 61 UNION ALL
SELECT 119 UNION ALL
SELECT 120 UNION ALL
SELECT 121 UNION ALL
SELECT 3599 UNION ALL
SELECT 3600 UNION ALL
SELECT 3601 UNION ALL
SELECT 86399


SELECT * FROM #t



SELECT id,CONVERT(VARCHAR(8),CONVERT(TIME,DATEADD(ss,TIME,'1900-01-01'))) AS t FROM #t


/*
id    t
1    00:00:59
2    00:01:00
3    00:01:01
4    00:01:59
5    00:02:00
6    00:02:01
7    00:59:59
8    01:00:00
9    01:00:01
10    23:59:59*/

------解决方案--------------------
其实看到别人回复了那么多我一开始不想恢复了。后来看到你还在发就回复而已,来者的才是你最开始想要的,我这个是根据你10楼的要求来转换而已