一个应收表，总是报转换成int时失败，之前能够正常查询，突然不顶用了，各位大佬帮忙看看，谢谢
谢谢各位大佬，这个问题已经解决了；
我通过注释排除的方法，找到了问题是因为有一个值里面输入的‘0’是全角的‘０’导致转换不过来，大家以后可以注意下！
SELECT
'销售订单'单据类型,0 交易号,T0.U_PROJECTCODE AS 项目编号,
T0.CardCode 客户号,
T0.CARDNAME AS 酒店名称,
ISNULL (T0.U_JXS,'空') AS 终端酒店名称,
ISNULL(CONVERT(VarChar(10),T0.TaxDate,23),'0000-00-00') AS 订单日期,
T0.DocCur 原币币别,
case when T0.DocCur='RMB' THEN Convert(decimal(18,2),T0.DocTotal) ELSE Convert(decimal(18,2),T0.DocTotalFC) END AS 订单原币金额,
Convert(decimal(18,2),T0.DocTotal) AS 订单金额,
ISNULL(T0.U_ZQ,'0') AS 账期,
ISNULL(CONVERT(VarChar(10),T1.TaxDate,23),'0000-00-00') AS 订单交货日期,
case WHEN ISNULL(CONVERT(VarChar(10),T1.TaxDate,23),'0')='0' THEN '0000-00-00' ELSE ISNULL(CONVERT(VarChar(10),T1.TaxDate+cast(ISNULL(T0.U_ZQ,0) as int),23),'0000-00-00')
END AS 到期应收日期,
ISNULL(Convert(decimal(18,2),JHZJE),'0')-ISNULL(Convert(decimal(18,2),THZJE),'0') AS 交货金额,
--T1.JHZJE AS 交货金额,
case when T0.DocCur='RMB'then Convert(decimal(18,2),T0.DocTotal)-ISNULL(Convert(decimal(18,2),JHZJE),'0')+ISNULL(Convert(decimal(18,2),THZJE),'0')
ELSE Convert(decimal(18,2),T0.DocTotal)-ISNULL(Convert(decimal(18,2),JHZJEFC),'0')*T0.DOCRATE+ISNULL(Convert(decimal(18,2),THZJEFC),'0')*T0.DOCRATE END AS 未出货金额,
Convert(decimal(18,2),T0.DocTotal) 年完成额和已签单未出货,
ISNULL(CONVERT(VarChar(10),T3.TaxDate,23),'0000-00-00') AS 预收款日期,
ISNULL(Convert(decimal(18,2),YSKZJE),'0') AS 预收款金额,
CASE WHEN CAST(T0.DocTotal as decimal(18,0))=0 THEN '0' ELSE ISNULL((cast(cast(YSKZJE/T0.DocTotal * 100 as decimal(18,0)) as varchar(10)) + '%'), '0') END as '预收款%',
ISNULL(CONVERT(VarChar(10),T4.TaxDate,23),'0000-00-00') AS 尾款款日期,
ISNULL(Convert(decimal(18,2),WKZJE), '0') AS 尾款款金额,
CASE WHEN CAST(T0.DocTotal as decimal(18,0))=0 THEN '0' ELSE ISNULL((cast(cast(WKZJE/T0.DocTotal * 100 as decimal(18,0)) as varchar(10)) + '%'), '0') END as '尾款收款%',
CASE WHEN T4.TaxDate IS NULL THEN ISNULL(datediff(DAY, T1.TaxDate,GETDATE()),'0')-ISNULL(T0.U_ZQ,'0') ELSE ISNULL(datediff(DAY, T1.TaxDate,T4.TaxDate),'0')-ISNULL(T0.U_ZQ,'0') END AS 超账期天数,
--ISNULL((T0.DocTotal-ISNULL(Convert(decimal(18,2),BZJZJE),'0')-ISNULL(Convert(decimal(18,2),WKZJE),'0')-ISNULL(Convert(decimal(18,2),YSKZJE),'0')),'0') AS 未收款金额,
--CASE WHEN ISNULL(T0.DocTotal,0)=0 THEN '0' ELSE (cast(cast((Convert(decimal(18,2),(T0.DocTotal-isnull(YSKZJE,0)-isnull(WKZJE,0)-isnull(BZJZJE,0)))/T0.DocTotal) * 100 as decimal(18,0)) as varchar(10)) + '%') END as '未收款%',
TT0.到期应收余额 未收款金额,
ISNULL(CONVERT(VarChar(30),T5.TaxDate,23),'0000-00-00') AS 质保金收款日期,
ISNULL(Convert(decimal(18,2),BZJZJE),'0') AS 质保金金额,
ISNULL(T0.U_ORDRTYPE,'空') AS 订单类型,
T0.BPLName 签单分支,
T0.Descr 实际分支
FROM
(SELECT T0.DocCur,T0.DOCRATE,T0.CARDCODE,T0.CARDNAME,T0.U_JXS,T0.U_PROJECTCODE,T0.DocTotal,T0.DocTotalFC,T0.TaxDate,T0.DocStatus,T1.BPLName,T0.U_ORDRTYPE,T2.Descr,T0.U_ZQ
FROM ORDR T0
INNER JOIN OBPL T1 on T0.BPLID=T1.BPLID
LEFT JOIN (SELECT T1.FldValue,t1.Descr FROM CUFD T0
INNER join UFD1 t1 on t0.TableID=t1.TableID and t0.FieldID=t1.FieldID
where t0.AliasID='SJFZ' and t0.TableID='ORDR') T2 ON T0.U_SJFZ=T2.FldValue
WHERE T0.canceled='N'
)T0 -- 订单信息
LEFT JOIN
(SELECT MAX(T1.TaxDate) TaxDate,SUM(T1.DOCTOTAL) AS JHZJE,SUM(T1.DOCTOTALFC) AS JHZJEFC,T1.U_PROJECTCODE FROM ODLN T1 WHERE canceled='N'group by T1.U_PROJECTCODE)
T1 -- 交货信息
ON T0.U_PROJECTCODE = T1.U_PROJECTCODE
LEFT JOIN
(SELECT MAX(T1.TaxDate) TaxDate,SUM(ISNULL(T1.DOCTOTAL,0)) AS THZJE,SUM(T1.DOCTOTALFC) AS THZJEFC,T1.U_PROJECTCODE FROM ORDN T1 WHERE canceled='N'group by T1.U_PROJECTCODE)
T111 -- 退货信息
ON T0.U_PROJECTCODE = T111.U_PROJECTCODE
LEFT JOIN
(
SELECT T3.Prjcode,MAX(T3.TaxDate) TaxDate,SUM(T3.DocTotal) AS YSKZJE,T3.U_SKLX FROM ORCT T3 WHERE U_SKLX in ('1','预收款') AND canceled='N' group by T3.Prjcode,T3.U_SKLX
)T3 -- 预收款信息
ON T0.U_PROJECTCODE = T3.Prjcode
LEFT JOIN
(SELECT T4.Prjcode,MAX(T4.TaxDate) TaxDate,SUM(T4.DocTotal) AS WKZJE,T4.U_SKLX FROM ORCT T4 WHERE U_SKLX in ('2','尾款') AND canceled='N' group by T4.Prjcode,T4.U_SKLX)T4 -- 尾款收款信息
ON T0.U_PROJECTCODE = T4.Prjcode
LEFT JOIN
(SELECT T5.Prjcode,MAX(T5.TaxDate) TaxDate,SUM(T5.DocTotal) AS BZJZJE,T5.U_SKLX FROM ORCT T5 WHERE U_SKLX in ('3','质保金') AND canceled='N' group by T5.Prjcode,T5.U_SKLX)T5 -- 保证金收款信息
ON T1.U_PROJECTCODE = T5.Prjcode
LEFT JOIN
(
SELECT tt0.Project 项目号
,SUM(TT0.到期应收余额) 到期应收余额
FROM(
SELECT '应收发票' 单据类型,t2.TransId,T2.Project
,T0.CardCode,T0.CardName,t4.DocCur
,CASE WHEN T4.DocCur='RMB' THEN T1.Debit-T1.Credit ELSE T1.FCDebit-T1.FCCredit END 发票金额
,T1.[BalDueDeb]-T1.[BalDueCred] 到期应收余额
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
INNER JOIN OINV T4 ON T4.DocNum=T1.BaseRef
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=13 --应收发票
union all
SELECT '收款单',t2.TransId,T2.Project
,T0.CardCode,T0.CardName,t4.DocCurr
,CASE WHEN T4.DocCurr='RMB' THEN T1.Debit-T1.Credit ELSE T1.FCDebit-T1.FCCredit END
,T1.[BalDueDeb]-T1.[BalDueCred]
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
INNER JOIN ORCT T4 ON T4.DocNum=T1.BaseRef
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=24 --收款单
union all
SELECT '应收贷项凭证',t2.TransId,T4.U_ProjectCode
,T0.CardCode,T0.CardName,t4.DocCur
,CASE WHEN T4.DocCur='RMB' THEN T1.Debit-T1.Credit ELSE T1.FCDebit-T1.FCCredit END AS 发票金额
, T1.[BalDueDeb]-T1.[BalDueCred]
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
INNER JOIN ORIN T4 ON T4.DocNum=T1.BaseRef
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=14 --应收贷项凭证
union all
SELECT '付款单',t2.TransId,T2.Project
,T0.CardCode,T0.CardName,t4.DocCurr,T1.Debit-T1.Credit,T1.[BalDueDeb]-T1.[BalDueCred]
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
INNER JOIN OVPM T4 ON T4.DocNum=T1.BaseRef
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=46 --付款单
union all
SELECT '日记账分录',t2.TransId,T2.Project
,T0.CardCode,T0.CardName,t0.Currency,T1.Debit-T1.Credit,T1.[BalDueDeb]-T1.[BalDueCred]
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=30 --日记账分录
union all
SELECT '内部对账',t2.TransId,T2.Project
,T0.CardCode,T0.CardName,'RMB',T1.Debit-T1.Credit,T1.[BalDueDeb]-T1.[BalDueCred]
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=321 --内部对账汇兑损益
)TT0
INNER JOIN OCRD TT1 ON TT0.CardCode=TT1.CardCode
INNER JOIN OCRG TT2 ON TT1.GroupCode=TT2.GroupCode
WHERE Tt0.Project!='' AND Tt0.Project IS NOT NULL
GROUP BY tt0.Project --,TT0.CardCode ,TT0.CardName
) TT0
ON TT0.项目号=T0.U_PROJECTCODE
INNER JOIN OCRD TT1 ON T0.CardCode=TT1.CardCode
WHERE T0.cardcode not in ('C8000P','C8100P','C8400P','C8200P','Y0664','Y0665','Y0666') and tT1.GroupCode!=115
UNION ALL
-----------------
SELECT TT0.单据类型,tt0.transid,tt0.Project 项目号,TT0.CardCode 客户号,TT0.CardName 酒店名称,'','','','0','0','0','', ISNULL(CONVERT(VarChar(10),tt0.DueDate,23),'0000-00-00') DueDate,'0'
,'0','0','','0','','','0','',''
,SUM(TT0.到期应收余额) 到期应收余额,'','0','','',''
FROM(
SELECT '应收发票' 单据类型,t2.TransId,T2.Project
,T0.CardCode,T0.CardName,t4.DocCur
,CASE WHEN T4.DocCur='RMB' THEN T1.Debit-T1.Credit ELSE T1.FCDebit-T1.FCCredit END 发票金额
,T1.[BalDueDeb]-T1.[BalDueCred] 到期应收余额,t4.DocDate+cast(ISNULL(T4.U_ZQ,0) as int) duedate
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
INNER JOIN OINV T4 ON T4.DocNum=T1.BaseRef
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=13 --应收发票
union all
SELECT '收款单',t2.TransId,T2.Project
,T0.CardCode,T0.CardName,t4.DocCurr
,CASE WHEN T4.DocCurr='RMB' THEN T1.Debit-T1.Credit ELSE T1.FCDebit-T1.FCCredit END
,T1.[BalDueDeb]-T1.[BalDueCred] ,t2.DueDate
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
INNER JOIN ORCT T4 ON T4.DocNum=T1.BaseRef
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=24 --收款单
union all
SELECT '应收贷项凭证',t2.TransId,T4.U_ProjectCode
,T0.CardCode,T0.CardName,t4.DocCur
,CASE WHEN T4.DocCur='RMB' THEN T1.Debit-T1.Credit ELSE T1.FCDebit-T1.FCCredit END AS 发票金额
, T1.[BalDueDeb]-T1.[BalDueCred],t2.DueDate
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
INNER JOIN ORIN T4 ON T4.DocNum=T1.BaseRef
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=14 --应收贷项凭证
union all
SELECT '付款单',t2.TransId,T2.Project
,T0.CardCode,T0.CardName,t4.DocCurr,T1.Debit-T1.Credit,T1.[BalDueDeb]-T1.[BalDueCred],t2.DueDate
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
INNER JOIN OVPM T4 ON T4.DocNum=T1.BaseRef
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=46 --付款单
union all
SELECT '日记账分录',t2.TransId,T2.Project
,T0.CardCode,T0.CardName,t0.Currency,T1.Debit-T1.Credit,T1.[BalDueDeb]-T1.[BalDueCred],t2.DueDate
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=30 --日记账分录
union all
SELECT '内部对账',t2.TransId,T2.Project
,T0.CardCode,T0.CardName,'RMB',T1.Debit-T1.Credit,T1.[BalDueDeb]-T1.[BalDueCred],t2.DueDate
FROM OCRD T0
INNER JOIN JDT1 T1 ON T0.CardCode=T1.ShortName
INNER JOIN OJDT T2 ON T1.TransId=T2.TransId
LEFT JOIN OSLP T3 ON T3.SlpCode=T0.SlpCode
WHERE T0.CardType='C' and t0.GroupCode!=115 and t0.cardcode not in ('C8000P','C8400P','C8200P','Y0664','Y0665','Y0666')
AND T1.TransType=321 --内部对账汇兑损益
)TT0
INNER JOIN OCRD TT1 ON TT0.CardCode=TT1.CardCode
INNER JOIN OCRG TT2 ON TT1.GroupCode=TT2.GroupCode
WHERE Tt0.Project='' or Tt0.Project IS NULL and TT1.cardcode not in ('C8000P','C8100P','C8400P','C8200P','Y0664','Y0665','Y0666') and tt1.GroupCode!=115
GROUP BY TT0.单据类型,tt0.Project ,TT0.CardCode ,TT0.CardName,tt0.TransId,tt0.DueDate
估计是这个有问题
T1.TaxDate+cast(ISNULL(T0.U_ZQ,0) as int)
如果T1.TaxDate是日期，怎么能跟int直接相加呢？
要使用DATEADD函数
DATEADD(day,cast(ISNULL(T0.U_ZQ,0) as int),T1.TaxDate)
https://www.w3school.com.cn/sql/func_dateadd.asp
代码太多就不看了，应该哪里数字类型缺少了引号，或者是无效的字符无法转换。
我通过注释排除的方法，找到了问题是因为有一个值里面输入的‘0’是全角的‘０’导致转换不过来，大家以后可以注意下！