我为什么要面对这个错误?这是非常恼人的,并试图解决它,但不起作用。
将数据类型varchar转换为bigint时出错
它引发有关此值2000100020502.的错误。
ALTER PROCEDURE [dbo].[Reports_UnDisbursedSalaryReport]
(
@FromDate SMALLDATETIME,
@ToDate SMALLDATETIME,
@OfficeID INT = 3,
@Type INT = 1,
@EmployerUniqueID VARCHAR(15) = '',
@EmployeeUniqueID VARCHAR(15) = ''
)
AS
BEGIN
SET @EmployerUniqueID =
CASE
WHEN LEN(@EmployerUniqueID) <=0
THEN NULL
ELSE @EmployerUniqueID
END
SET @EmployeeUniqueID =
CASE
WHEN LEN(@EmployeeUniqueID) <= 0
THEN NULL
ELSE @EmployeeUniqueID
END
DECLARE @Branches TABLE (BranchID INT)
IF @Type = 1
BEGIN
INSERT INTO @Branches
SELECT BranchID
FROM vw_OrganizationTree
WHERE OrganizationID = @OfficeID
END
IF @Type = 2
BEGIN
INSERT INTO @Branches
SELECT BranchID
FROM vw_OrganizationTree
WHERE CompanyID = @OfficeID
END
IF @Type = 3
BEGIN
INSERT INTO @Branches
SELECT BranchID
FROM vw_OrganizationTree
WHERE BranchID = @OfficeID
END
SELECT
DATEADD(HOUR, 4, PF.EntryDateTime) 'EntryDateTime',
C.CompanyName, B.BranchName,
@FromDate as 'Fromdate', @ToDate as 'Todate',
E.EmployerName, RE.EmployeeName, RE.EmployeeUniqueID,
FE.IncomeFixedComponent, FE.IncomeVariableComponent,
S.StatusDescription
FROM
File_EdrEntries FE
JOIN
RegisteredEmployees RE ON RE.EmployeeUniqueID = FE.EmployeeUniqueID
JOIN
Employers E ON E.ID = RE.Employer_ID
JOIN
Branches B ON B.BranchID = E.Branch_ID
JOIN
companies C ON C.COMPANYID = B.COMPANY_ID
JOIN
Statuses S ON S.StatusID = FE.Status_ID
JOIN
PAFFiles PF ON PF.ID = FE.PAFFile_ID
WHERE
PF.EntryDateTime >= @FromDate
AND PF.EntryDateTime < DATEADD(DAY, 1, @ToDate)
AND Status_ID IN (1, 2)
AND E.Branch_id IN (SELECT BranchID From @Branches)
AND CAST(E.EmployerID AS BIGINT) = ISNULL(CAST(@EmployerUniqueID AS BIGINT), CAST(E.EmployerID AS BIGINT))
AND CAST(RE.EmployeeUniqueID AS BIGINT) = ISNULL(CAST(@EmployeeUniqueID AS BIGINT), CAST(RE.EmployeeUniqueID AS BIGINT))
END
发布于 2016-08-17 09:10:44
您可以使用try_convert函数。我假设您使用的是sql server 2012及以上.
SELECT DATEADD(HOUR, 4, PF.EntryDateTime) 'EntryDateTime'
,C.CompanyName
,B.BranchName
,@FromDate AS 'Fromdate'
,@ToDate AS 'Todate'
,E.EmployerName
,RE.EmployeeName
,RE.EmployeeUniqueID
,FE.IncomeFixedComponent
,FE.IncomeVariableComponent
,S.StatusDescription
FROM File_EdrEntries FE
JOIN RegisteredEmployees RE ON RE.EmployeeUniqueID = FE.EmployeeUniqueID
JOIN Employers E ON E.ID = RE.Employer_ID
JOIN Branches B ON B.BranchID = E.Branch_ID
JOIN companies C ON C.COMPANYID = B.COMPANY_ID
JOIN Statuses S ON S.StatusID = FE.Status_ID
JOIN PAFFiles PF ON PF.ID = FE.PAFFile_ID
WHERE PF.EntryDateTime >= @FromDate
AND PF.EntryDateTime < DATEADD(DAY, 1, @ToDate)
AND Status_ID IN (
1
,2
)
AND E.Branch_id IN (
SELECT BranchID
FROM @Branches
)
AND try_convert(BIGINT, E.EmployerID) = ISNULL(try_convert(BIGINT, @EmployerUniqueID), try_convert(BIGINT, E.EmployerID))
AND try_convert(BIGINT, RE.EmployeeUniqueID) = ISNULL(try_convert(BIGINT, @EmployeeUniqueID), try_convert(BIGINT, RE.EmployeeUniqueID))
如果try_convert函数在您的情况下工作,那么您的数据包含字母数字字符。
发布于 2016-08-17 09:04:09
可能是在转换列时,而不是变量:CAST(E.EmployerID AS BIGINT)
或CAST(RE.EmployeeUniqueID AS BIGINT)
。您确定所有的Employers.EmployerID
或RegisteredEmployees.EmployeeUniqueID
都可以转换为bigint
吗?
发布于 2016-08-17 11:32:38
select convert( bigint , 2000100020502)
https://stackoverflow.com/questions/38992195
复制相似问题