将varchar值转换为Server中的数据类型int时，转换失败

Question

我搞错了

将varchar值'ORB2.0000000001‘转换为数据类型int时，转换失败。

你能帮我解决这个问题吗？存储过程在开发过程中运行良好，但在另一台机器上，我得到了该错误。

ALTER PROCEDURE [dbo].[EMR_GetReportData](
    @FromDate DATETIME
    , @ToDate DATETIME
    , @Code varchar(20) = NULL
    , @ReportType INT
) AS
BEGIN
    IF(@ReportType = 7)
    BEGIN
        SELECT DISTINCT
            PAI.OP_NO
            , PAI.Pat_Name
            , CONVERT(VARCHAR, PVD.Visit_Date, 106) AS DOA
            , DM.Name AS DrName
            , D.department
        FROM
            dbo.Pat_Info PAI
            INNER JOIN dbo.Pat_Visit_Details PVD ON PAI.OP_NO = PVD.OP_No
            INNER JOIN dbo.departments D ON PVD.Department_id = D.department_id
            INNER JOIN dbo.DoctorMain DM ON PVD.DrName COLLATE Latin1_General_CI_AI = DM.Code
        WHERE
            PVD.[Type] = 'OPD'
            AND PVD.Visit_Date BETWEEN @FromDate AND @ToDate
    END
    ELSE IF(@ReportType = 8)
    BEGIN
        SELECT
            PAI.OP_NO
            , PAI.Pat_Name
            ,  CONVERT(VARCHAR, PVD.DOA, 106) AS DOA
            , DM.Name AS DrName
            , D.department
        FROM
            dbo.Pat_Info PAI
            INNER JOIN dbo.Pat_Visit_Details PVD ON PAI.OP_NO = PVD.OP_No
            INNER JOIN dbo.departments D ON PVD.Department_id = D.department_id
            INNER JOIN dbo.DoctorMain DM ON PVD.DrName COLLATE Latin1_General_CI_AI = DM.Code
        WHERE
            PVD.[Type] = 'IPD'
            AND PVD.DOA BETWEEN @FromDate AND @ToDate
    END
    ELSE IF(@ReportType = 9)
    BEGIN
        SELECT
            D.Cr_No
            , PAI.Pat_Name
            , D.Code
            , CONVERT(VARCHAR, D.Visit_Date, 106) AS Visit_Date
            , D.[Description]
        FROM
            dbo.Diagnosis1 D
            INNER JOIN dbo.Pat_Info PAI ON D.Cr_No = PAI.OP_NO
        WHERE
            D.Code = @Code
            AND D.Visit_Date BETWEEN @FromDate AND @ToDate
    END
END

Answer 1

varchar值包含一些ORB在ORB2.0000000001中，不能转换为int。若要删除错误，请从报表字符串中删除这些字符，然后转换为int。

只有当任何字符串仅包含int (0、1、2、3、4、5、6、7、8、9)时，才能将其转换为整数。

此外，如果要在值中使用十进制，请使用浮点数据类型。参见这里 c#的一些数据类型手册