sql server 2008 Error converting data type nvarchar to datetime.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hello friends...

i have a stored procedure as follows:

SQL

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 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

SQL

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 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:

SQL

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

Posted 29-Oct-14 20:37pm

TAUSEEF KALDANE

Add a Solution

Comments

Shweta N Mishra 30-Oct-14 5:40am

Why you are converting your @sdate paramater of SP_FirstDistil to dd/mm/yyyy.

check this

declare @sdate date='2014-10-29'

select convert(varchar(20),@sdate,103)

It results to 29/10/2014 and when you are passing this to the SP_Firstdistil as date it results to an error.

You can try for convert(varchar(20),@sdate,101)

Herman<T>.Instance 30-Oct-14 6:43am

Set your comment as solution so I can upvote it!

Shweta N Mishra 30-Oct-14 6:46am

done :)

Shweta N Mishra 30-Oct-14 7:46am

thank you :)

Laiju k 30-Oct-14 7:00am

you are passing string to datetime or viceversa

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Answer 1 · 2014-10-30T00:46:00

Why you are converting your @sdate paramater of SP_FirstDistil to dd/mm/yyyy.

check this

declare @sdate date='2014-10-29'

select convert(varchar(20),@sdate,103)

It results to 29/10/2014 and when you are passing this to the SP_Firstdistil as date it results to an error.

You can try for convert(varchar(20),@sdate,101)

Maciej Los · Answer 2 · 2014-10-30T12:53:00

Instead of:

SQL

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'

use this:

SQL

SET @EndDate = DATEADD(day, DATEDIFF(day, 0, @SDate), '06:00:00')

In case of troubles, use SET DATETIMEFORMAT[^] command at the begining of SP, depending on SQL server settings.

SQL

BEGIN
    SET DATETIMEFORMAT dmy;
    --or
    --SET DATETIMEFORMAT mdy;
    --or
    --SET DATETIMEFORMAT ymd;

sql server 2008 Error converting data type nvarchar to datetime.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0