sql server 2008将数据类型nvarchar转换为datetime时出错. [英] sql server 2008 Error converting data type nvarchar to datetime.
本文介绍了sql server 2008将数据类型nvarchar转换为datetime时出错.的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
你好朋友...
我有一个存储过程，如下所示:
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: <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
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: <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:
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
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
推荐答案
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)
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)
Instead of:
Instead of:
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:
use this:
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.
In case of troubles, use SET DATETIMEFORMAT[^] command at the begining of SP, depending on SQL server settings.
BEGIN
SET DATETIMEFORMAT dmy;
--or
--SET DATETIMEFORMAT mdy;
--or
--SET DATETIMEFORMAT ymd;这篇关于sql server 2008将数据类型nvarchar转换为datetime时出错.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
