SQL 函数返回值精确度问题
我有一个获取时间差的函数：
gethoursvs()在这个函数中会执行这样的一条语句：
cast(datediff(mi,'2014-01-15 10:59:21.510','2014-01-15 13:13:16.167')/60.0 as decimal(15,2))
用select语句查找出来
select cast(datediff(mi,'2014-01-15 10:59:21.510','2014-01-15 13:13:16.167')/60.0 as decimal(15,2))
这个语句我获取的值是2.23
但是执行
select CAST(dbo.gethoursvs('2014-01-15 10:59:21.510','2014-01-15 13:13:16.167')AS nvarchar)
返回的确实 2 。
gethoursvs（）这个函数的返回值类型是 decimal类型的。
请各位大神指教
[解决办法]
改成这样试试；
select CAST(dbo.gethoursvs('2014-01-15 10:59:21.510','2014-01-15 13:13:16.167')AS nvarchar(10))ALTER function [dbo].[gethoursvs](@stime datetime,@etime datetime)
returns decimal(15,2)
as
begin
declare @hstime decimal(15,2)
declare @hetime decimal(15,2)
declare @result decimal(15,2)
select @hstime=Ltrim(datepart(hh,@stime))
select @hetime=Ltrim(datepart(hh,@etime))
if (@hstime<8 and @hetime<17 and @hetime>7)
begin
set @result=cast(datediff(mi,8,@etime)/60.0 as decimal(15,2))
end
else if (@hstime<8 and @hetime>17)
begin
set @result=8
end
else if (@hstime<8 and @hetime<8)
begin
set @result=0
end
else if (@hstime>=17 and @hetime>=17)
begin
set @result=0
end
else if (@hstime>=17 and @hetime<17 and @hetime>=8)
begin
set @result=cast(datediff(mi,8,@etime)/60.0 as decimal(15,2))
end
else if (@hstime>=17 and @hetime<8)
begin
set @result=0
end
else if (@hstime>=8 and @hstime<17 and @hetime>=17)
begin
set @result=cast(datediff(mi,@stime,17)/60.0 as decimal(15,2))
end
else if (@hstime>=8 and @hstime<=17 and @hetime<8)
begin
set @result=cast(datediff(mi,@stime,17)/60.0 as decimal(15,2))
end
else if (@hstime>@hetime AND @hetime>=8 AND @hstime <17)
begin
set @result=cast(datediff(mi,@stime,@etime)/60.0 as decimal(15,2))+9
end
else if (@hstime>@hetime AND @hetime>=8 AND @hstime >17)
begin
set @result=cast(datediff(mi,8,@etime)/60.0 as decimal(15,2))+9
end
else
begin
set @result=cast(datediff(mi,@stime,@etime)/60.0 as decimal(15,2))
end
return @result
end
GO