不允许从数据类型 datetime 到 int 的隐性转换

我写了一个存储过程：
select @q=max(分组序号)from oinv_ID where kp_rq between @begintime and @endtime
--convert(nvarchar(10),@begintime,120)

set @sql='select distinct * from'
+'(select distinct t0.docentry,b.kp_id,t0.cardname,t0.numatcard ,t8.name as 片区,t0.docentry as 合同编号 ,t0.docdate as 合同日期,t1.linenum, '
+' t1.itemcode,t1.quantity,isnull(cast(t33.quantity  as numeric ),0) as 按时间统计交货数,isnull(cast(t1.delivrdqty  as numeric),0) as 系统交货数量, '
+' 按时间交货备注=case '
+' when isnull(cast(t33.quantity  as numeric),0)>=t1.quantity  then ''交货完毕'''+'when isnull(cast(t33.quantity  as numeric),0)=''0''  then ''完全没交货'''+' else ''交货进行中'''+ ' end, '
+' 按系统交货备注=case '+ 'when isnull(cast(t1.delivrdqty  as numeric),0)>=t1.quantity  then ''交货完毕'''+ ' when isnull(cast(t1.delivrdqty  as numeric),0)=''0''  then ''完全没交货'''+' else ''交货进行中''' +' end,'
+ ' T1.PriceAfVAT as 物料价格,(T1.PriceAfVAT*t1.quantity) as 物料总价,'
+'isnull(cast(t5.doctotal  as money),0)as 合同总金额,'
+'isnull(cast(t5.paidsum  as money),0)as 已经付款金额,'
+'isnull(cast(t5.paidtotal as money),0) as 按时间统计收款,'
+'(isnull(cast(t5.paidsum  as money),0)-isnull(cast(t5.paidtotal as money),0)) as 支付相差金额,'
+'(t5.doctotal -isnull(cast(t5.paidtotal as money),0)) as 按时间统计未支付金额,'
+'t6.交货时间1,t6.交货时间2,t6.交货时间3,t6.交货时间4,t6.交货时间5,t6.交货时间6,t6.交货时间7,t6.交货时间8,t6.交货时间9,t6.交货时间10,t6.交货时间11 ,t9.name,t9.tel1 '
+'from oinv t0 inner join inv1 t1  '
+'on t0.docentry = t1.docentry left outer join (select t3.numatcard ,t3.docdate,t3.docentry,t4.linenum,t3.doctotal, '
+'t21.paidtotal ,'
+'t3.paidsum from oinv t3 inner join inv1 t4 '
+' on t3.docentry = t4.docentry '

+'left outer  join  #temp_orct001 t21 on t3.numatcard=t21.numatcard01 and t3.docentry=t21.docentry '

+' where t4.linenum = ''0'')t5 on '
+' t0.numatcard = t5.numatcard and '
+' t0.docentry = t5.docentry and t1.linenum = t5.linenum '

+' left outer join  #temp_hxodln21 t33 on t1.docentry=t33.baseentry and t1.linenum=t33.baseline and t0.numatcard=t33.numatcard '

+' left outer   join #1 t6 on t6.baseref =  t0.docentry and t6.baseline = t1.linenum '

+' inner join  [dbo].[@PQB]  t8 on  T0.U_pq = T8.Code  '
+' left outer join ocpr t9 on t0.cardcode = t9.cardcode and t0.cntctcode = t9.cntctcode '

+'left outer join '
set @sql=@sql+ ' (select isnull(cast(sum(kp_je)as money),0) as 开票总金额 ,t.kp_id , t.kp_hth from( select * from oinv_kp where (kp_rq between '''+left(convert(  nvarchar(23),@begintime,121),10)+ ''' and '''+left(convert( nvarchar(23),@endtime,121),10)+''' ))t group by t.kp_id,t.kp_hth) b on t0.docentry=b.kp_id )x left outer join '+'(SELECT kp_id ,kp_hth ,'

set @i=1
while @i<=@q

begin

set @sql= @sql +' MAX(CASE 分组序号 WHEN '+cast(@i as varchar(10))

set @sql= @sql +' THEN rtrim(kp_hm) ELSE ''0'' END) AS 第'

set @sql= @sql+cast(@i as varchar(10))+'次开票号码 ,'     --逗号放后面因为还要接b列进行列转行

set @sql= @sql +' MAX(CASE 分组序号 WHEN '+cast(@i as varchar(10))     --逗号放前因为还要接b列是最后一行进行列转行sql没有逗号了

set @sql= @sql +' THEN rtrim(kp_dw) ELSE ''0'' END) AS 第'

set @sql= @sql+cast(@i as varchar(10))+'次开票单位 ,'

set @sql= @sql +' MAX(CASE 分组序号 WHEN '+cast(@i as varchar(10))     --逗号放前因为还要接b列是最后一行进行列转行sql没有逗号了

set @sql= @sql +' THEN kp_je ELSE 0 END) AS 第'

set @sql= @sql+cast(@i as varchar(10))+'次开票金额 ,'

set @sql= @sql +' MAX(CASE 分组序号 WHEN '+cast(@i as varchar(10))     --逗号放前因为还要接b列是最后一行进行列转行sql没有逗号了

set @sql= @sql +' THEN rtrim(kp_kh) ELSE ''0'' END) AS 第'

set @sql= @sql+cast(@i as varchar(10))+'次开票客户 ,'

set @sql= @sql +' MAX(CASE 分组序号 WHEN '+cast(@i as varchar(10))     --逗号放前因为还要接b列是最后一行进行列转行sql没有逗号了

set @sql= @sql +' THEN rtrim(kp_bz) ELSE ''0'' END) AS 第'

set @sql= @sql+cast(@i as varchar(10))+'次开票备注 ,'

set @sql= @sql +' MAX(CASE 分组序号 WHEN '+cast(@i as varchar(10))     --逗号放前因为还要接b列是最后一行进行列转行sql没有逗号了

set @sql= @sql +' THEN rtrim(kp_sh) ELSE ''0'' END) AS 第'

set @sql= @sql+cast(@i as varchar(10))+'次开票审核 ,'

set @sql= @sql +' MAX(CASE 分组序号 WHEN '+cast(@i as varchar(10))     --逗号放前因为还要接b列是最后一行进行列转行sql没有逗号了

set @sql= @sql +' THEN kp_rq ELSE ''2000-01-01'' END) AS 第'
set @sql= @sql+cast(@i as varchar(10))+'次开票日期 ,'

set @i=@i+1

end

set @sql=@sql+' 0 as line  from oinv_ID where (kp_rq between '''+left(convert(nvarchar(23),@begintime,121),10)+ ''' and '''+left(convert( nvarchar(23),@endtime,121),10)+''') GROUP BY kp_id,kp_hth)z on x.kp_id=z.kp_id and x.linenum=z.line where x.numatcard <>''作废'''+'and  x.docentry= 765 '--or x.docentry=765 '
--set @strsql=@strsql +' 1 as line from oinv_ID where (kp_rq between '''+left(convert(nvarchar(23),@rq1 ,121),10)+''' and''' +left(convert(nvarchar(23),@rq2,121),10)+''') GROUP BY kp_id,kp_hth )t on t.kp_id=z.docentry '

exec (@sql)

在sql2000的查询分析器自带的右键调试存储过程的工具调试没有错误，我print@sql 语句为：
select distinct * from(select distinct t0.docentry,b.kp_id,t0.cardname,t0.numatcard ,t8.name as 片区,t0.docentry as 合同编号 ,t0.docdate as 合同日期,t1.linenum,  t1.itemcode,t1.quantity,isnull(cast(t33.quantity  as numeric ),0) as 按时间统计交货数,isnull(cast(t1.delivrdqty  as numeric),0) as 系统交货数量,  按时间交货备注=case  when isnull(cast(t33.quantity  as numeric),0)>=t1.quantity  then '交货完毕'when isnull(cast(t33.quantity  as numeric),0)='0'  then '完全没交货' else '交货进行中' end,  按系统交货备注=case when isnull(cast(t1.delivrdqty  as numeric),0)>=t1.quantity  then '交货完毕' when isnull(cast(t1.delivrdqty  as numeric),0)='0'  then '完全没交货' else '交货进行中' end, T1.PriceAfVAT as 物料价格,(T1.PriceAfVAT*t1.quantity) as 物料总价,isnull(cast(t5.doctotal  as money),0)as 合同总金额,isnull(cast(t5.paidsum  as money),0)as 已经付款金额,isnull(cast(t5.paidtotal as money),0) as 按时间统计收款,(isnull(cast(t5.paidsum  as money),0)-isnull(cast(t5.paidtotal as money),0)) as 支付相差金额,(t5.doctotal -isnull(cast(t5.paidtotal as money),0)) as 按时间统计未支付金额,t6.交货时间1,t6.交货时间2,t6.交货时间3,t6.交货时间4,t6.交货时间5,t6.交货时间6,t6.交货时间7,t6.交货时间8,t6.交货时间9,t6.交货时间10,t6.交货时间11 ,t9.name,t9.tel1 from oinv t0 inner join inv1 t1  on t0.docentry = t1.docentry left outer join (select t3.numatcard ,t3.docdate,t3.docentry,t4.linenum,t3.doctotal, t21.paidtotal ,t3.paidsum from oinv t3 inner join inv1 t4  on t3.docentry = t4.docentry left outer  join  #temp_orct001 t21 on t3.numatcard=t21.numatcard01 and t3.docentry=t21.docentry  where t4.linenum = '0')t5 on  t0.numatcard = t5.numatcard and  t0.docentry = t5.docentry and t1.linenum = t5.linenum  left outer join  #temp_hxodln21 t33 on t1.docentry=t33.baseentry and t1.linenum=t33.baseline and t0.numatcard=t33.numatcard  left outer   join #1 t6 on t6.baseref =  t0.docentry and t6.baseline = t1.linenum  inner join  [dbo].[@PQB]  t8 on  T0.U_pq = T8.Code   left outer join ocpr t9 on t0.cardcode = t9.cardcode and t0.cntctcode = t9.cntctcode left outer join  (select isnull(cast(sum(kp_je)as money),0) as 开票总金额 ,t.kp_id , t.kp_hth from( select * from oinv_kp where (kp_rq between '2010-01-01' and '2010-12-31' ))t group by t.kp_id,t.kp_hth) b on t0.docentry=b.kp_id )x left outer join (SELECT kp_id ,kp_hth , MAX(CASE 分组序号 WHEN 1 THEN rtrim(kp_hm) ELSE '0' END) AS 第1次开票号码 , MAX(CASE 分组序号 WHEN 1 THEN rtrim(kp_dw) ELSE '0' END) AS 第1次开票单位 , MAX(CASE 分组序号 WHEN 1 THEN kp_je ELSE 0 END) AS 第1次开票金额 , MAX(CASE 分组序号 WHEN 1 THEN rtrim(kp_kh) ELSE '0' END) AS 第1次开票客户 , MAX(CASE 分组序号 WHEN 1 THEN rtrim(kp_bz) ELSE '0' END) AS 第1次开票备注 , MAX(CASE 分组序号 WHEN 1 THEN rtrim(kp_sh) ELSE '0' END) AS 第1次开票审核 , MAX(CASE 分组序号 WHEN 1 THEN kp_rq ELSE '2000-01-01' END) AS 第1次开票日期 , MAX(CASE 分组序号 WHEN 2 THEN rtrim(kp_hm) ELSE '0' END) AS 第2次开票号码 , MAX(CASE 分组序号 WHEN 2 THEN rtrim(kp_dw) ELSE '0' END) AS 第2次开票单位 , MAX(CASE 分组序号 WHEN 2 THEN kp_je ELSE 0 END) AS 第2次开票金额 , MAX(CASE 分组序号 WHEN 2 THEN rtrim(kp_kh) ELSE '0' END) AS 第2次开票客户 , MAX(CASE 分组序号 WHEN 2 THEN rtrim(kp_bz) ELSE '0' END) AS 第2次开票备注 , MAX(CASE 分组序号 WHEN 2 THEN rtrim(kp_sh) ELSE '0' END) AS 第2次开票审核 , MAX(CASE 分组序号 WHEN 2 THEN kp_rq ELSE '2000-01-01' END) AS 第2次开票日期 , 0 as line  from oinv_ID where (kp_rq between '2010-01-01' and '2010-12-31') GROUP BY kp_id,kp_hth)z on x.kp_id=z.kp_id and x.linenum=z.line where x.numatcard <>'作废'and  x.docentry= 765

@RETURN_VALUE = 0

没有错，，，

但是在自己写的调用程序
declare @begintime datetime , @endtime datetime
--@rq1 varchar(20) , @rq2 varchar(20)
, @Sql   nvarchar(4000),@q int ,@i int

--select * from oinv_kp where kp_rq between convert(nvarchar(20),@begintime,120) and convert(nvarchar(20),@endtime,120)
set @i=1
set @q=5
set @begintime= '2000-12-01'
set @endtime= '2012-12-31'

--print @sql
exec cwxmb_pk0911 @q,@i, @begintime ,@endtime,@sql

一直提示：
服务器: 消息 257，级别 16，状态 2，过程 cwxmb_pk0911，行 0
不允许从数据类型 datetime 到 int 的隐性转换。请使用 CONVERT 函数来运行此查询。

请大侠帮忙看下

11 个解决方案

#1

你这个是折磨别人眼睛呀
看报错信息就是有datetime类型到int的转换错误,注意一下case语句

#2

时间类型转换成整型，这个不行吧！




declare @s datetime

set @s = '2010-09-13'

select convert(int,@s,120)



--结果

40432


--根本就不对。

是不是搞错了？

#3

1楼正解、了解广大人民群众的感受

#4

不好意思啊，，大家伙别介意，，，贴急了,,没有整理，，，我case语句我自己看不出什么错误，，，哎！

#5

引用 2 楼 hao1hao2hao3 的回复:

时间类型转换成整型，这个不行吧！

SQL code

declare @s datetime
set @s = '2010-09-13'
select convert(int,@s,120)

--结果
40432

--根本就不对。

是不是搞错了？

大侠你这个代表什么意思？

#6

引用 5 楼 redphase 的回复:

引用 2 楼 hao1hao2hao3 的回复:
时间类型转换成整型，这个不行吧！

SQL code

declare @s datetime
set @s = '2010-09-13'
select convert(int,@s,120)

--结果
40432

--根本就不对。

是不是搞错了？

大侠你这个代表什么意思？

呵呵！就是说你的逻辑运算有问题，用datetime类型的值去和整型的值进行比较，这能比吗？

#7

我看到你这个问题，我就晕了，我都实在没有勇气看下去了

#8

~~我有勇气，但看得太快，一下拉到底了。~~
考虑一下2F的回复。

#9

另外 @Sql nvarchar(4000)
似乎短了些，建议设为8000

#10

眼睛花了

#11

谢谢大家伙啊，，，
我找到原因了：
set @sql= @sql +' THEN kp_rq ELSE '+cast('2000-01-01'as varchar(20))+' END) AS 第'
set @sql= @sql+cast(@i as varchar(10))+'次开票日期 ,'

'2000-01-01' 在用exec（@sql）输出时候不一定是直接能直接为时间处理的，因为动态的sql语句里面是整理成字符串才能用exec(@sql)输出的，必须用cast语句转换才不会报错！

#1