将数值转换为数据类型varchar的算术溢出错误。 [英] Arithmetic overflow error converting numeric to data type varchar.

问题描述

我创建了以下功能 

I have created the following function 

USE [MtsGoData]
GO

/****** Object:  UserDefinedFunction [dbo].[goConvertMinutesToHours]    Script Date: 22/05/2019 07:09:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER FUNCTION [dbo].[goConvertMinutesToHours]
(
    @Minutes int 
)
RETURNS decimal(15,2)

AS
BEGIN
declare @hours  nvarchar(20)

SET @hours = 
    CASE
	 WHEN @minutes = 0 then '0.00'
	  
    ELSE 
        CAST((CONVERT(NUMERIC(18, 2), floor(@Minutes / 60) + ((@Minutes % 60) / 100.0))) AS VARCHAR(2)) 
    END

return cast(@hours as  decimal(10,2));
END


GO

当我运行下面给出的以下sql时， 它显示错误'算术溢出错误将数字转换为数据类型varchar。请帮助<

When I run the following sql given below ,  it is showing the error 'Arithmetic overflow error converting numeric to data type varchar. Please help

选择[dbo]。[goConvertMinutesToHours]（300）;

select [dbo].[goConvertMinutesToHours](300);

polachan

推荐答案

Hi
polachan，

 

请尝试以下脚本。

alter  FUNCTION [dbo].[goConvertMinutesToHours]
(
    @Minutes int 
)
RETURNS decimal(15,2)

AS
BEGIN
declare @hours  nvarchar(20)

SET @hours = 
    CASE
	 WHEN @minutes = 0 then '0.00'
	  
    ELSE 
        CAST((CONVERT(NUMERIC(18, 2), floor(@Minutes / 60) + ((@Minutes % 60) / 100.0))) AS VARCHAR(20)) 
    END

return cast(@hours as  decimal(10,2));
END
GO

select [dbo].[goConvertMinutesToHours](300);
/*
---------------------------------------
5.00
*/

 

 

Best此致，

Best Regards,

Rachel

这篇关于将数值转换为数据类型varchar的算术溢出错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！