转换时出现sql错误 [英] sql error on conversion

问题描述

我有下面的查询，它给出错误"将nvarchar值'1年'转换为数据类型int时转换失败。"我为转换做了演员，不知道为什么会出现这个错误，请指教。

Hi, I have the below the query, it gives the error "Conversion failed when converting the nvarchar value '1 year' to data type int." I did the cast for the conversion, not sure why it gives this error, please advise.

Select  'Prosecutions Opened' as type,
Cast(Count(*) as nvarchar(max)) as Total,
( 
Select Cast(Count(*) as nvarchar(max)) from FilteredIncident 
where casetypecode =4 and (createdon between DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) and '4/12/2019')
) as YTD 
from FilteredIncident as inc
where inc.casetypecode =4 and (inc.createdon between '1/1/2019' and '4/12/2019')



union all 



Select  'Prosecutions Closed' as type,
Cast(count(*) as nvarchar(max)) as Total,
( 
Select Cast(count(*) as nvarchar(max)) from FilteredIncident 
where casetypecode =4 and (recoserv_resolutiondate between DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) and '4/12/2019')
) as YTD 
from FilteredIncident as inc
where inc.casetypecode =4 and (inc.recoserv_resolutiondate between '1/1/2019' and '4/12/2019')






union all
Select  'Prosecutions In Progress' as type,
Cast(count(*) as nvarchar(max)) as Total,
( 
Select Cast(count(*) as nvarchar(max)) from FilteredIncident 
where casetypecode =1 and statuscode = 1 and (recoserv_resolutiondate between DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) and '4/12/2019')
) as YTD 
from FilteredIncident as inc
where inc.casetypecode =1 and inc.statuscode = 1  and (inc.recoserv_resolutiondate between '1/1/2019' and '4/12/2019')



union all
Select 
'Probation' as Type,
Cast(Cast(Sum(Cast(recoserv_probationyear as int))as nvarchar(100))+'Year'  + Cast(Sum(Cast(recoserv_probationmonth as int))as nvarchar(100)) ++'Month' as nvarchar(max)) as Total,
(Select Cast(Cast(Sum(Cast(recoserv_probationyear as int)) as nvarchar(100))+'Year' + Cast(Sum(Cast(recoserv_probationmonth as int))as nvarchar(100)) ++'Month' as nvarchar(max)) as pryear
from FilteredIncident as inc inner join Filteredrecoserv_charges as chrg on inc.Incidentid = chrg.recoserv_Incidentchargesid
where  inc.casetypecode =4 and (inc.createdon between  DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) and '4/12/2019')) as YTD
from FilteredIncident as inc inner join Filteredrecoserv_charges as chrg on inc.Incidentid = chrg.recoserv_Incidentchargesid
where  inc.casetypecode =4 and (inc.createdon between '1/1/2019' and '4/12/2019')

推荐答案

如果您可以发布样本DDL，那将会很棒要测试的数据。

It will be great if you can post sample DDL with data to test.

我尝试使用下面的示例DDL进行查询，但没有给出任何问题。

I tried your query with sample DDL below and it did not give any issue.

create table FilteredIncident (Incidentid int,casetypecode int,createdon datetime,recoserv_resolutiondate datetime,statuscode int)
create table Filteredrecoserv_charges (recoserv_Incidentchargesid int,recoserv_probationyear int,recoserv_probationmonth int);
go

insert into FilteredIncident values (1,4,'20190412','20190412',1),(2,4,'20190413','20190413',1),(3,4,'20190414','20190414',1)
insert into Filteredrecoserv_charges values (1,2019,04);
go

请在您的DDL上发布样本数据，很乐意提供帮助！

Please post your DDL with sample data, will be happy to assist!

这篇关于转换时出现sql错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！