sql server 2008 Error converting data type nvarchar to datet

hello friends...

i have a stored procedure as follows:

USE [cheminova] GO /****** Object:  StoredProcedure [dbo].[SP_Firstdistil]    Script Date: 10/30/2014 11:55:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:		 -- Create date:  -- Description:	 -- ============================================= ALTER PROCEDURE [dbo].[SP_Firstdistil]  	-- Add the parameters for the stored procedure here     --   @StartDate varchar(50)=Null, 	   --@EndDate varchar(50)=Null 	   @SDate DateTime AS BEGIN 	-- SET NOCOUNT ON added to prevent extra result sets from 	-- interfering with SELECT statements. 	SET NOCOUNT ON;       -- Insert statements for procedure here     declare @StartDate varchar(50)     declare @EndDate varchar(50)     set @EndDate=CONVERT(varchar,DATEPART(month,@SDate))+'/'+CONVERT(varchar,DATEPART(DAY,@SDate))+'/'+CONVERT(varchar,DATEPART(YEAR,@SDate))+' 06:00:00'     set @StartDate=CONVERT(varchar,DATEPART(month,DATEADD(day,-1,@SDate)))+'/'+CONVERT(varchar,DATEPART(DAY,DATEADD(day,-1,@SDate)))+'/'+CONVERT(varchar,DATEPART(YEAR,DATEADD(day,-1,@SDate)))+' 06:00:00' 	Begin 	--table to store data of each tag for a day 	create table #tempval 	( 	    [ID] [int] IDENTITY(1,1) NOT NULL, 		tagname varchar(100),         [timestamp] Varchar(50),         tagval varchar(50),         quality varchar(20) 	) 	create table #tempval1 	( 	    [ID] [int] IDENTITY(1,1) NOT NULL, 		tagname varchar(100),         [timestamp] Varchar(50),         tagval varchar(50),         quality varchar(20) 	) 	create table #tempval2 	( 	    [ID] [int] IDENTITY(1,1) NOT NULL, 		tagname varchar(100),         [timestamp] Varchar(50),         tagval varchar(50),         quality varchar(20) 	) 	create table #tempval3 	( 	    [ID] [int] IDENTITY(1,1) NOT NULL, 		tagname varchar(100),         [timestamp] Varchar(50),         tagval varchar(50),         quality varchar(20) 	) 	--table to store average of each tag for a day 	create table #tagavg 	( 	[ID] [int] IDENTITY(1,1) NOT NULL, 	avgtag float 	) 				create table #temp1 				( 				[ID] [int] IDENTITY(1,1) NOT NULL, 				--[Timestamp] varchar(50), 				[Value][float] NULL 				) 				create table #temp2 				( 				[ID] [int] IDENTITY(1,1) NOT NULL, 				--[Timestamp] varchar(50), 				[Value][float] NULL 				) 				create table #temp3 				( 				[ID] [int] IDENTITY(1,1) NOT NULL, 				--[Timestamp] varchar(50), 				[Value][float] NULL 				) 				create table #temp4 				( 				[ID] [int] IDENTITY(1,1) NOT NULL, 				--[Timestamp] varchar(50), 				[Value][float] NULL 				) 				create table #temp5 				( 				[ID] [int] IDENTITY(1,1) NOT NULL, 				--[Timestamp] Datetime, 				[Value][float] NULL 				) 	create table #tempdigival 	( 	    [ID] [int] IDENTITY(1,1) NOT NULL, 		[timestamp] varchar(30), 		tagval varchar(30) 	) 	 	create table #tempsum 	( 		tagval varchar(30), 		calevent float 	) 	 	create table #tagtemp 	( 	    [ID] [int] IDENTITY(1,1) NOT NULL, 		[timestamp] datetime, 		tagname varchar(200), 		descrip varchar(200), 		unit varchar(10), 		tagval varchar(38), 		calevent float 	) 	create table #totalizervalue 	( 	    [ID] [int] IDENTITY(1,1) NOT NULL, 		tagname varchar(200), 		waqt varchar(30), 		tagval varchar(30) 	) 	create table #FinalResult 		( 		   [ID] [int] IDENTITY(1,1) NOT NULL, 				tag1 float, 				tag2 float, 				tag3 float, 				tag4 float, 				tag5 float, 				tag6 float 		) 	--to store reading 	--select @StartDate as startdate,@EndDate as enddate      DECLARE @SQL as varchar(1000)     Declare @Tagname varchar(300)     Declare @tagVal float     Declare @calval float     declare @avgtag float     declare @X1 float     declare @Y1 float     declare @Z1 float     declare @X2 float     declare @Y2 float     declare @Z2 float     declare @X3 float     declare @Y3 float     declare @Z3 float     declare @X4 float     declare @Y4 float     declare @Z4 float     declare @X5 float     declare @Y5 float     declare @Z5 float     declare @X6 float     declare @Y6 float     declare @Z6 float     declare @X7 float     declare @Y7 float     declare @Z7 float     declare @X8 float     declare @Y8 float     declare @Z8 float     declare @query varchar(500) 	declare @starttime varchar(30) 	declare @endtime varchar(30) 	declare @coltagname varchar(300) 	declare @coltimestamp datetime 	declare @colavg varchar(38) 	declare @prevtime varchar(30) 	declare @currtime varchar(30) 	declare @breakflag int 	declare @firsttimeflag int 	set @firsttimeflag=0 	declare @final float 	declare @initial float 	declare @event float 	Declare @digitalsql varchar(1000) 	set @final=0 	set @initial=0      set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''      set  StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime          SELECT tagname, timestamp  ,value,quality FROM ihRawData       WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1302'''' '')'      INSERT INTO #tempval1 EXEC(@SQL)      Set @Tagname='ADMIN-PC.Channel5.Device1.LIC1302'         --Select @Tagname as TagName, [timestamp],CAST(tagval as float)as TagValue,         --Cast(tagval as float)*11.08 as CalValue ,quality from         --#tempval1 ORDER BY tagname, [timestamp]         select top 1 @X1= Cast(tagval as float) from #tempval1 order by ID asc         --select @X1 as FirstVal                 select top 1 @Y1=Cast(tagval as Float) from #tempval1 order by ID Desc         --Select @Y1 as SecondVal         set @Z1=(@Y1-@X1)*11.08         Insert into #temp1 values (@Z1)         --SELECT @Z1 as LI1603 from #temp1              set @sql ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''      set  StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=Calculated          SELECT tagname, timestamp  ,value,quality FROM ihRawData       WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1304'''' '')'      INSERT INTO #tempval2 EXEC(@SQL)        /*Select  @Tagname as TagName,[timestamp],CAST(tagval as float)as TagValue,         Cast(tagval as float)*6.02 as CalValue ,quality from      #tempval2 ORDER BY tagname, [timestamp]*/      select top 1 @X2= Cast (tagval as Float) from #tempval2 order by ID asc         select top 1 @Y2=Cast (tagval as Float) from #tempval2 order by ID desc         set @Z2=(@Y2-@X2)*6.02         Insert into #temp2 values (@Z2)         --SELECT @Z2 as LI1604 from #temp2       set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''      set  StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime          SELECT tagname, timestamp  ,value,quality FROM ihRawData       WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1403'''' '')'      INSERT INTO #tempval3 EXEC(@SQL)      SET @Tagname='ADMIN-PC.Channel5.Device1.LIC1403'         /* Select @Tagname as TagName,[timestamp],CAST(tagval as float)as TagValue,         Cast(tagval as float)*4.80 as CalValue ,quality from         #tempval3 ORDER BY tagname, [timestamp]*/         select top 1 @X3= Cast (tagval as Float) from #tempval3 order by ID asc         --select @X3 as FirstValue         Select top 1 @Y3=Cast (tagval as Float) from #tempval3 order by ID Desc         --select @Y3 as secondvalue         set @Z3=(@Y3-@X3)*4.80         Insert into #temp3 values (@Z3)         --SELECT @Z3 as LI1607 from #temp3                  set @query='select * from openquery(chemhist,''set StartTime="'+@starttime+'",EndTime="'+@endtime+'",SamplingMode=RawByTime,RowCount=0 select timestamp,value from ihrawdata where tagname=ADMIN-PC.Channel5.Device1.P65'')' 		insert into #tempdigival exec(@query) 		select @breakflag= COUNT(*) from #tempdigival where tagval=1 		set @query='select * from openquery(chemhist,''set StartTime="'+@starttime+'",EndTime="'+@endtime+'",SamplingMode=RawByTime,RowCount=0 select tagname,timestamp,value from ihrawdata where tagname=ADMIN-PC.Channel5.Device1.LI1505'')'		 		insert into #totalizervalue exec(@query) 		--take lost time from manual 		if (@breakflag>0)		 		begin 			declare C1 cursor 			for select timestamp from #tempdigival where tagval=1 			open C1 			fetch next from C1 into @currtime 			while @@FETCH_STATUS=0 			begin 				if (@firsttimeflag=0) 				begin 					set @firsttimeflag=1 				select @initial=cast(tagval as float)from #totalizervalue where CONVERT(datetime2,waqt)=CONVERT(datetime2,dateadd(SECOND,10, CONVERT(datetime2,@starttime))) 					select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@currtime) order by waqt desc 					set @final=(@final-@initial)  					set @event=@final * 10 					set @colavg=CONVERT(varchar,@event) 					insert into #tempsum values(@colavg,@final)  					set @prevtime=@currtime 				end 				else 				begin 					select top 1 @initial=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)>=CONVERT(datetime2,@prevtime) 					select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@currtime) order by waqt desc 					set @final=(@final-@initial) 					set @colavg=CONVERT(varchar,@final) 					insert into #tempsum values(@colavg,@final)  					set @prevtime=@currtime 				end 				fetch next from C1 into @currtime 			end 			select top 1 @initial=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)>=CONVERT(datetime2,@prevtime) 			select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@enddate) order by waqt desc 			set @final=(@final-@initial) 			set @event=@final * 10 			set @colavg=CONVERT(varchar,@event) 			insert into #tempsum values(@colavg,@final)  			close C1 			deallocate C1 			select @colavg=SUM(CAST(tagval as float)) from #tempsum 			--truncate table #tempsum 			set @coltagname='ADMIN-PC.Channel5.Device1.LIC1503' 			set @coltimestamp=CONVERT(datetime,@endtime) 			insert into #tagtemp values(@coltimestamp,@coltagname,'PDS slurry Flow Totaliser - 9A','m3',@colavg,@final) 		end 		else 		begin 			select @initial=cast(tagval as float)from #totalizervalue where CONVERT(datetime2,waqt)=CONVERT(datetime2,dateadd(SECOND,10, CONVERT(datetime2,@starttime))) 			select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@enddate) order by waqt desc	 			set @final=(@final-@initial) 			set @event=@final * 10 			set @colavg=CONVERT(varchar,@event) 			set @coltagname='ADMIN-PC.Channel5.Device1.LI1505' 			set @coltimestamp=CONVERT(datetime,@endtime) 			insert into #tagtemp values(@coltimestamp,@coltagname,'PDS slurry Flow Totaliser - 9A','m3',@colavg,@final) 		end         --SELECT @Z4 as LI1608 from #temp4             set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,''      set  StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime          SELECT tagname, timestamp  ,value,quality FROM ihRawData       WHERE tagname =''''ADMIN-PC.Channel5.Device1.FIQ1302'''' '')'      INSERT INTO #tempval EXEC(@SQL)      --SET @Tagname='ADMIN-PC.Chem.Device1.LI1609'      --   Select @Tagname,[timestamp],CAST(tagval as float)as TagValue,      --   Cast(tagval as float)*73.30 as CalValue ,quality from      --#tempval ORDER BY tagname, [timestamp]      --select @calval=Cast (tagval as Float)*73.30 from #tempval      --   Insert into #tagtemp values (@calval,@tagVal)         select top 1 @X5= Cast (tagval as Float) from #tempval order by ID asc         --select @X5 as FirstValue         Select top 1 @Y5=Cast (tagval as Float) from #tempval order by ID Desc         --select @Y5 as secondvalue         set @Z5=(@Y5-@X5)*73.30         Insert into #temp5 values (@Z5)         --SELECT @Z5 as LI1609 from #temp5      END      set @avgtag=cast((isnull(@Z1,0)+isnull(@Z2,0)+isnull(@Z3,0)+isnull(@Z4,0)) as float)/cast((nullif((@Z5),0)) as float)      insert into #tagavg values (@avgtag)      --select * from #tagavg      --inner join on table      Begin      insert into #finalResult select isnull(t1.Value,0) as  R_21LT,isnull(t2.Value,0) as  R_22LT,isnull(t3.Value,0) as R_24LT, isnull(t4.calevent,0) as B_22LT,isnull(t5.Value,0) as DETA_FIQ ,isnull(t6.avgtag,0) as avgtag from #temp1 as t1      Inner join #temp2 as t2      on t2.ID=t1.ID      Inner join #temp3 as t3      on t3.ID=t2.ID      Inner join #tagtemp as t4      on t4.ID=t3.ID      inner join #temp5 as t5      on t5.ID=t4.ID      inner join #tagavg as t6      on t6.ID=t5.ID      select tag1,tag2,tag3,tag4,tag5,tag6 from #FinalResult      End       END

and i have another stored procedure as follows

USE [cheminova] GO /****** Object:  StoredProcedure [dbo].[SP_FinalYieldtemp]    Script Date: 10/30/2014 10:22:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:		 -- Create date:  -- Description:	 -- ============================================= ALTER PROCEDURE [dbo].[SP_FinalYieldtemp]  	-- Add the parameters for the stored procedure here 	@sdate datetime 	 AS BEGIN 	-- SET NOCOUNT ON added to prevent extra result sets from 	-- interfering with SELECT statements. 	SET NOCOUNT ON; 	--SET @sdate=convert(varchar (10),@sdate,103) declare @sql as varchar(1000) Begin 		create table #Temp1 		( 		   [ID] [int] IDENTITY(1,1) NOT NULL, 				tag1 float, 				tag2 float, 				tag3 float, 				tag4 float, 				tag5 float, 				tag6 float 		) END     -- Insert statements for procedure here 	SET @SQL ='SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=ADMIN-PC;Trusted_Connection=yes;'', ''SET FMTONLY OFF; EXEC [cheminova].[dbo].[SP_Firstdistil] "'+convert(varchar(20),@sdate,103)+'" '')' --select @SQL EXEC (@SQL) --INSERT into  #Temp1 EXEC (@SQL) END

and i execute SP_FinalYieldtemp as follows:

exec SP_FinalYieldtemp '2014-10-29'

but i m getting following error

Msg 8114, Level 16, State 5, Procedure SP_Firstdistil, Line 0
Error converting data type nvarchar to datetime.

what i have done here is, i have called SP_Firstdistil procedure with parameter @sdate in SP_FinalYieldtemp in stored procedure.

when i executed SP_Firstdistil procedure, it gave me proper output.
i m very confused in SET @SQL statement in SP_FinalYieldtemp procedure.
plz help to resolve it