sql server 2008 Error converting data type nvarchar to datetime -

sql server 2008 Error converting data type nvarchar to datetime -

i have stored procedure 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: <author,,pragya> -- create date: <create date,,> -- description: <description,,> -- ============================================= alter procedure [dbo].[sp_firstdistil] -- add together parameters stored procedure here -- @startdate varchar(50)=null, --@enddate varchar(50)=null @sdate datetime begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; -- insert statements 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 store info of each tag 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 store average of each tag 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 startdate,@enddate enddate declare @sql 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 openquery(chemhist,'' set starttime = '''''+ @startdate +''''', endtime = '''''+ @enddate +''''', rowcount=0, samplingmode=rawbytime select tagname, timestamp ,value,quality ihrawdata tagname =''''admin-pc.channel5.device1.lic1302'''' '')' insert #tempval1 exec(@sql) set @tagname='admin-pc.channel5.device1.lic1302' --select @tagname tagname, [timestamp],cast(tagval float)as tagvalue, --cast(tagval float)*11.08 calvalue ,quality --#tempval1 order tagname, [timestamp] select top 1 @x1= cast(tagval float) #tempval1 order id asc --select @x1 firstval select top 1 @y1=cast(tagval float) #tempval1 order id desc --select @y1 secondval set @z1=(@y1-@x1)*11.08 insert #temp1 values (@z1) --select @z1 li1603 #temp1 set @sql ='select tagname, timestamp , value,quality openquery(chemhist,'' set starttime = '''''+ @startdate +''''', endtime = '''''+ @enddate +''''', rowcount=0, samplingmode=calculated select tagname, timestamp ,value,quality ihrawdata tagname =''''admin-pc.channel5.device1.lic1304'''' '')' insert #tempval2 exec(@sql) /*select @tagname tagname,[timestamp],cast(tagval float)as tagvalue, cast(tagval float)*6.02 calvalue ,quality #tempval2 order tagname, [timestamp]*/ select top 1 @x2= cast (tagval float) #tempval2 order id asc select top 1 @y2=cast (tagval float) #tempval2 order id desc set @z2=(@y2-@x2)*6.02 insert #temp2 values (@z2) --select @z2 li1604 #temp2 set @sql ='select tagname, timestamp , value,quality openquery(chemhist,'' set starttime = '''''+ @startdate +''''', endtime = '''''+ @enddate +''''', rowcount=0, samplingmode=rawbytime select tagname, timestamp ,value,quality ihrawdata tagname =''''admin-pc.channel5.device1.lic1403'''' '')' insert #tempval3 exec(@sql) set @tagname='admin-pc.channel5.device1.lic1403' /* select @tagname tagname,[timestamp],cast(tagval float)as tagvalue, cast(tagval float)*4.80 calvalue ,quality #tempval3 order tagname, [timestamp]*/ select top 1 @x3= cast (tagval float) #tempval3 order id asc --select @x3 firstvalue select top 1 @y3=cast (tagval float) #tempval3 order id desc --select @y3 secondvalue set @z3=(@y3-@x3)*4.80 insert #temp3 values (@z3) --select @z3 li1607 #temp3 set @query='select * openquery(chemhist,''set starttime="'+@starttime+'",endtime="'+@endtime+'",samplingmode=rawbytime,rowcount=0 select timestamp,value ihrawdata tagname=admin-pc.channel5.device1.p65'')' insert #tempdigival exec(@query) select @breakflag= count(*) #tempdigival tagval=1 set @query='select * openquery(chemhist,''set starttime="'+@starttime+'",endtime="'+@endtime+'",samplingmode=rawbytime,rowcount=0 select tagname,timestamp,value ihrawdata tagname=admin-pc.channel5.device1.li1505'')' insert #totalizervalue exec(@query) --take lost time manual if (@breakflag>0) begin declare c1 cursor select timestamp #tempdigival tagval=1 open c1 fetch next c1 @currtime while @@fetch_status=0 begin if (@firsttimeflag=0) begin set @firsttimeflag=1 select @initial=cast(tagval float)from #totalizervalue convert(datetime2,waqt)=convert(datetime2,dateadd(second,10, convert(datetime2,@starttime))) select top 1 @final=cast(tagval float) #totalizervalue convert(datetime2,waqt)<=convert(datetime2,@currtime) order waqt desc set @final=(@final-@initial) set @event=@final * 10 set @colavg=convert(varchar,@event) insert #tempsum values(@colavg,@final) set @prevtime=@currtime end else begin select top 1 @initial=cast(tagval float) #totalizervalue convert(datetime2,waqt)>=convert(datetime2,@prevtime) select top 1 @final=cast(tagval float) #totalizervalue convert(datetime2,waqt)<=convert(datetime2,@currtime) order waqt desc set @final=(@final-@initial) set @colavg=convert(varchar,@final) insert #tempsum values(@colavg,@final) set @prevtime=@currtime end fetch next c1 @currtime end select top 1 @initial=cast(tagval float) #totalizervalue convert(datetime2,waqt)>=convert(datetime2,@prevtime) select top 1 @final=cast(tagval float) #totalizervalue convert(datetime2,waqt)<=convert(datetime2,@enddate) order waqt desc set @final=(@final-@initial) set @event=@final * 10 set @colavg=convert(varchar,@event) insert #tempsum values(@colavg,@final) close c1 deallocate c1 select @colavg=sum(cast(tagval float)) #tempsum --truncate table #tempsum set @coltagname='admin-pc.channel5.device1.lic1503' set @coltimestamp=convert(datetime,@endtime) insert #tagtemp values(@coltimestamp,@coltagname,'pds slurry flow totaliser - 9a','m3',@colavg,@final) end else begin select @initial=cast(tagval float)from #totalizervalue convert(datetime2,waqt)=convert(datetime2,dateadd(second,10, convert(datetime2,@starttime))) select top 1 @final=cast(tagval float) #totalizervalue convert(datetime2,waqt)<=convert(datetime2,@enddate) order 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 #tagtemp values(@coltimestamp,@coltagname,'pds slurry flow totaliser - 9a','m3',@colavg,@final) end --select @z4 li1608 #temp4 set @sql ='select tagname, timestamp , value,quality openquery(chemhist,'' set starttime = '''''+ @startdate +''''', endtime = '''''+ @enddate +''''', rowcount=0, samplingmode=rawbytime select tagname, timestamp ,value,quality ihrawdata tagname =''''admin-pc.channel5.device1.fiq1302'''' '')' insert #tempval exec(@sql) --set @tagname='admin-pc.chem.device1.li1609' -- select @tagname,[timestamp],cast(tagval float)as tagvalue, -- cast(tagval float)*73.30 calvalue ,quality --#tempval order tagname, [timestamp] --select @calval=cast (tagval float)*73.30 #tempval -- insert #tagtemp values (@calval,@tagval) select top 1 @x5= cast (tagval float) #tempval order id asc --select @x5 firstvalue select top 1 @y5=cast (tagval float) #tempval order id desc --select @y5 secondvalue set @z5=(@y5-@x5)*73.30 insert #temp5 values (@z5) --select @z5 li1609 #temp5 end set @avgtag=cast((isnull(@z1,0)+isnull(@z2,0)+isnull(@z3,0)+isnull(@z4,0)) float)/cast((nullif((@z5),0)) float) insert #tagavg values (@avgtag) --select * #tagavg --inner bring together on table begin insert #finalresult select isnull(t1.value,0) r_21lt,isnull(t2.value,0) r_22lt,isnull(t3.value,0) r_24lt, isnull(t4.calevent,0) b_22lt,isnull(t5.value,0) deta_fiq ,isnull(t6.avgtag,0) avgtag #temp1 t1 inner bring together #temp2 t2 on t2.id=t1.id inner bring together #temp3 t3 on t3.id=t2.id inner bring together #tagtemp t4 on t4.id=t3.id inner bring together #temp5 t5 on t5.id=t4.id inner bring together #tagavg t6 on t6.id=t5.id select tag1,tag2,tag3,tag4,tag5,tag6 #finalresult end end

and have stored procedure 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: <author,,name> -- create date: <create date,,> -- description: <description,,> -- ============================================= alter procedure [dbo].[sp_finalyieldtemp] -- add together parameters stored procedure here @sdate datetime begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; --set @sdate=convert(varchar (10),@sdate,103) declare @sql 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 procedure here set @sql ='select * 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 #temp1 exec (@sql) end

and execute sp_finalyieldtemp follows:

exec sp_finalyieldtemp '2014-10-29'

but m getting next error

msg 8114, level 16, state 5, procedure sp_firstdistil, line 0 error converting info type nvarchar datetime.

what have done here is, have called sp_firstdistil procedure parameter @sdate in sp_finalyieldtemp in stored procedure.

when executed sp_firstdistil procedure, gave me proper output. m confused in set @sql statement in sp_finalyieldtemp procedure. plz help resolve it

103 ambiguous style. if must go on string-heavy style, @ to the lowest degree utilize unambiguous styles in conversions.

the unambiguous styles 112 (for date) , 126 (for date , time). so:

set @sql ='select * openrowset(''sqlncli'', ''server=admin-pc;trusted_connection=yes;'', ''set fmtonly off; exec [cheminova].[dbo].[sp_firstdistil] "'+ convert(varchar(20),@sdate,112)+'" '')'

although i'd recommend switching using sp_executesql allows pass parameters using there natural types rather converting strings.

other points comments, not straight relating problem/solution:

1) using many temporary tables sign you're doing wrongtm - feels you've broken task downwards lots of little procedural steps. instead, in sql, should seek describe entire task want (rather how it) , allow query optimizer effort find best way accomplish result.

2) avoid using sp_ prefix when naming procedures

3) stop doing string manipulation. instance, this:

set @enddate=convert(varchar,datepart(month,@sdate))+'/'+convert(varchar,datepart(day,@sdate))+'/'+convert(varchar,datepart(year,@sdate))+' 06:00:00'

which appears attempting create "today @ 6am" replaced by:

set @enddate = dateadd(day,datediff(day,'20000101',@sdate),'2001-01-01t06:00:00')

which performs 2 simple pieces of date arithmetic rather constructing (again) ambiguous date format , hoping sql server converts datetime correctly.

4) , similarly, 1 time date has been constructed, can dateadd(day,-1,@enddate) produce "yesterday @ 6am" rather doing either of conversions again.

sql sql-server-2008 database-administration