我现在有一列时间字段,如下所示: time
1.2
1.4
1.5
我现在想将具体数字转换成小时,也就是说小数点前面的数字不变,后面的数字都除上60,则以上数字转换成小时后变成:1.2=1.(20/60)=1.33 1.4=1.(40/60)=1.67 ......则以上数字变成如下:time
1.33
1.67
1.83
1.2
1.4
1.5
我现在想将具体数字转换成小时,也就是说小数点前面的数字不变,后面的数字都除上60,则以上数字转换成小时后变成:1.2=1.(20/60)=1.33 1.4=1.(40/60)=1.67 ......则以上数字变成如下:time
1.33
1.67
1.83
declare @1 table([time] decimal(18,2))
Insert @1
select 1.2 union all
select 1.4 union all
select 1.5
Select Convert(numeric(18,2),Floor(Time)+(Time-Floor(Time))/.6) As Time from @1
/*
Time
---------------------------------------
1.33
1.67
1.83
*/
1.33
1.67
1.83
declare @table table ([time] nvarchar(5))
insert into @table select '1.2'
union all select '1.4'
union all select '1.5'
select LEFT([time],1) + SUBSTRING( str(RIGHT([time],1)/6.0 ,4,2),2,4) from @table
from
(
select 1.2 as ttime
union all
select 1.3
union all
select 1.4
union all
select 10.2
union all
select 1.25
union all
select 11.23
) tselect left(cast(ttime as char),charindex('.',cast(ttime as char))-1) + '.' + right(left(cast(cast(substring(cast(ttime as char),charindex('.',cast(ttime as char))+1,2) as decimal) / 60 as char),4),2)
from #tmp