select ltrim(时间*60/60)+'小时;'+ltrim(时间*60%60)+'分钟' from tb
截取小数点前后的数据 sum起来再转换。
select substring(convert(nvarchar(10),t),0,charindex('.',convert(nvarchar(10),t))) as h, substring(convert(nvarchar(10),t),charindex('.',convert(nvarchar(10),t))+1,len(convert(nvarchar(10),t))) as m from #temp
汇总用sum() 。 转换你的时间
create table #temp (时间 decimal(4,2)) insert into #temp values(0.30) insert into #temp values(4.30) insert into #temp values(1.30) insert into #temp values(5.00) select ltrim(SUM(cast(parsename(时间,2) as int)))+'小时'+ltrim(SUM(cast(parsename(时间,1) as int)))+'分钟' from #temp drop table #temp /*-------------------------------- 10小时90分钟(1 行受影响)*/
create table tb (时间 decimal(4,1)) insert into tb values(0.30) insert into tb values(4.30) insert into tb values(1.30) insert into tb values(5.00) goselect sum(cast(时间-0.11 as int) +cast((时间-cast(时间-0.11 as int))*100/60 as int) +cast((时间-cast(时间-0.11 as int))*100%60/100 as decimal(12,2))) from tbdrop table tb/****************--------------------------------------- 11.30(1 行受影响)
insert into #temp values(0.2) 如果这样的话,0.2是分钟,就会算错
小F insert into #temp values(0.2)插入这个就也不对了
IF NOT OBJECT_ID(N'Tempdb..#T') IS NULL DROP TABLE #T;go CREATE TABLE #T ( tDate DECIMAL(4,2))
GO INSERT INTO #T ( tDate) SELECT 7.30 UNION ALL SELECT 2.35 UNION ALL SELECT 1.52 UNION ALL SELECT 4.20 DECLARE @h INT ; --小时 DECLARE @m INT ; --分钟
SELECT @h= SUM(a.tDate) FROM (SELECT CONVERT(INT ,SUBSTRING(CONVERT(VARCHAR(20),tDate),0, PATINDEX('%.%', CONVERT(VARCHAR(20),tDate))) ) tDate FROM #T) AS a
SELECT @m= SUM(a.tDate) FROM (SELECT CONVERT(INT ,SUBSTRING(CONVERT(VARCHAR(20),tDate),PATINDEX('%.%', CONVERT(VARCHAR(20),tDate))+1, LEN(tDate)-PATINDEX('%.%', CONVERT(VARCHAR(20),tDate)) ) ) tDate FROM #T) AS a SELECT @h AS 小时, @m AS 分钟
select ltrim(时间*60/60)+'小时;'+ltrim(时间*60%60)+'分钟'
from tb
select substring(convert(nvarchar(10),t),0,charindex('.',convert(nvarchar(10),t))) as h,
substring(convert(nvarchar(10),t),charindex('.',convert(nvarchar(10),t))+1,len(convert(nvarchar(10),t))) as m
from #temp
insert into #temp values(0.30)
insert into #temp values(4.30)
insert into #temp values(1.30)
insert into #temp values(5.00)
select
ltrim(SUM(cast(parsename(时间,2) as int)))+'小时'+ltrim(SUM(cast(parsename(时间,1) as int)))+'分钟'
from
#temp
drop table #temp
/*--------------------------------
10小时90分钟(1 行受影响)*/
create table tb (时间 decimal(4,1))
insert into tb values(0.30)
insert into tb values(4.30)
insert into tb values(1.30)
insert into tb values(5.00)
goselect sum(cast(时间-0.11 as int)
+cast((时间-cast(时间-0.11 as int))*100/60 as int)
+cast((时间-cast(时间-0.11 as int))*100%60/100 as decimal(12,2)))
from tbdrop table tb/****************---------------------------------------
11.30(1 行受影响)
如果这样的话,0.2是分钟,就会算错
insert into #temp values(0.2)插入这个就也不对了
DROP TABLE #T;go CREATE TABLE #T
( tDate DECIMAL(4,2))
GO INSERT INTO #T ( tDate)
SELECT 7.30 UNION ALL
SELECT 2.35 UNION ALL
SELECT 1.52 UNION ALL
SELECT 4.20 DECLARE @h INT ; --小时
DECLARE @m INT ; --分钟
SELECT @h= SUM(a.tDate) FROM (SELECT CONVERT(INT ,SUBSTRING(CONVERT(VARCHAR(20),tDate),0, PATINDEX('%.%', CONVERT(VARCHAR(20),tDate))) ) tDate FROM #T) AS a
SELECT @m= SUM(a.tDate) FROM (SELECT CONVERT(INT ,SUBSTRING(CONVERT(VARCHAR(20),tDate),PATINDEX('%.%', CONVERT(VARCHAR(20),tDate))+1, LEN(tDate)-PATINDEX('%.%', CONVERT(VARCHAR(20),tDate)) ) ) tDate FROM #T) AS a
SELECT @h AS 小时, @m AS 分钟
SELECT CONVERT(VARCHAR(20),(@m/60+@h))+'.'+CONVERT(VARCHAR(20), @m%60)