从字符串向 datetime 转换时失败
我在执行存储过程的时候 提示了这段话、
-- exec proc_hj_rpt_item_dept_dsaout 'convert(varchar(10),a.date_out,120) >= ''2013-01-12'' and convert(varchar(10),a.date_out,120) <= ''2013-11-13'' '
alter PROC [dbo].[proc_hj_rpt_item_dept_dsaout] (@var_parm VARCHAR(2000))
AS
DECLARE @var_sql1 VARCHAR(max),@var_sql2 VARCHAR(max),@var_sql3 VARCHAR(max),@var_sql4 VARCHAR(max),@var_group VARCHAR(200) ,
@var_parm1 VARCHAR(max),@var_parm2 VARCHAR(max),
@var_parm3 VARCHAR(max),@var_group1 VARCHAR(200),@date_lyearbegin DATETIME,
@date_lyearend DATETIME,@date_lmonthbegin DATETIME,@date_lmonthend DATETIME
--参数解析
--同期的时间
SET @date_lyearbegin = DATEADD(year,-1,CAST (SUBSTRING(@var_parm,charindex('a.date_out,120) >= ',@var_parm)+22,10) AS DATETIME))
SET @date_lyearend = DATEADD(year,-1,CAST (SUBSTRING(@var_parm,charindex('a.date_out,120) <= ',@var_parm)+22,10) AS DATETIME))
--环比的时间
SET @date_lmonthbegin = DATEADD(month,-1,CAST (SUBSTRING(@var_parm,charindex('a.date_out,120) >= ',@var_parm)+21,10) AS DATETIME))
SET @date_lmonthend = DATEADD(month,-1,CAST (SUBSTRING(@var_parm,charindex('a.date_out,120) <= ',@var_parm)+21,10) AS DATETIME))
--同期的参数
SET @var_parm2 =SUBSTRING(@var_parm,0,charindex('a.date_out,120) >= ',@var_parm)+21) +convert(varchar(10),@date_lyearbegin,120)+''' '
+ SUBSTRING(@var_parm,charindex('a.date_out,120) >= ',@var_parm)+33,LEN(' and convert(varchar(10),a.date_out,120) <= ')+1) +convert(varchar(10),@date_lyearend,120) + ''' ' + SUBSTRING(@var_parm,charindex('a.date_out,120) <= ',@var_parm)+33,999)
--环比的参数
SET @var_parm3 =SUBSTRING(@var_parm,0,charindex('a.date_out,120) >= ',@var_parm)+21) +convert(varchar(10),@date_lmonthbegin,120) +''' '
+ SUBSTRING(@var_parm,charindex('a.date_out,120) >= ',@var_parm)+33,LEN(' and convert(varchar(10),a.date_out,120) <= ')+1) +convert(varchar(10),@date_lmonthend,120) + ''' ' + SUBSTRING(@var_parm,charindex('a.date_out,120) <= ',@var_parm)+33,999)
PRINT @var_parm2
--SET @var_group= 'c.id_cate3,c.name_cate3'
SET @var_sql1=' Sselect a.id_item,b.name_item,a.id_dept,c.name_dept,sum(a.dec_qty)dec_qty
INTO #Temp1
from dsaout_03 a,ctlm1020 b,ctlm1003 c
where 1=1 and '+@var_parm+'
and a.id_item =b.id_item
and a.id_dept =c.id_dept
group by a.id_item,b.name_item,a.id_dept,c.name_dept
'
SET @var_sql2=' Sselect a.id_item,b.name_item,a.id_dept,c.name_dept,sum(a.dec_qty)dec_qty
INTO #Temp2
from dsaout_03 a,ctlm1020 b,ctlm1003 c
where 1=1 and '+@var_parm2+'
and a.id_item =b.id_item
and a.id_dept =c.id_dept
group by a.id_item,b.name_item,a.id_dept,c.name_dept
'
SET @var_sql3=' Sselect a.id_item,b.name_item,a.id_dept,c.name_dept,sum(a.dec_qty)dec_qty
INTO #Temp3
from dsaout_03 a,ctlm1020 b,ctlm1003 c
where 1=1 and '+@var_parm3+'
and a.id_item =b.id_item
and a.id_dept =c.id_dept
group by a.id_item,b.name_item,a.id_dept,c.name_dept
'
set @var_sql4 =' select a.id_item,a.name_item,a.id_dept,a.name_dept,isnull(a.dec_qty,0)dec_qty,isnull(b.dec_qty,0)dec_qty2,c.dec_qty dec_qty3
FROM #temp1 a
LEFT JOIN #Temp2 b ON a.id_item =b.id_item AND a.id_dept= b.id_dept
LEFT JOIN #Temp3 c ON a.id_item =c.id_item AND a.id_dept= c.id_dept
order by a.id_item;
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
'
PRINT @var_sql1
PRINT @var_sql2
PRINT @var_sql4
EXEC (@var_sql1+@var_sql2+@var_sql3+@var_sql4)