语法错误将字符串转换为smalldatetime数据 [英] Syntax error converting character string to smalldatetime data
问题描述
大家好
当我从查询页面执行存储过程时，会收到此错误.
这是我的存储过程:
Hi All
I receive this error when I execute stored procedure from query page.
This is my stored procedure :
Alter Proc [dbo].[SP_CompPrd]
(
@CompPrd_Temp nvarchar(128),
@Prv_strt_yr smalldatetime,
@Cur_End_Prd smalldatetime
)
As
Declare @MySQL nvarchar(2000);
Declare @MyFdate nvarchar(10);
Declare @MyTdate nvarchar(10);
begin
Set @MySQL = N''Drop table '' + @CompPrd_Temp
exec sp_executesql @MySQL
print @MySQL
---------------------------------------------------
Set @MySQL = N''''
Set @MySQL = N''SELECT Memo1, CustomerCategory, ItemCode, CurrDay_Qty, PrvDay_Qty, CurrPrd_Qty, PrvPrd_Qty, CurrYr_Qty, PrvYr_Qty
INTO ['' + @CompPrd_Temp + '']
FROM CompPrd_Temp
WHERE Memo1 <> Memo1''
exec sp_executesql @MySQL
print @MySQL
----------------------------------------------------
set @MyFdate = '''' + convert(nvarchar(10),@Prv_strt_yr,102)+ ''''
Set @MyTdate = '''' + convert(nvarchar(10),@Cur_End_Prd,102) + ''''
Set @MySQL = N''''
Set @MySQL = N''INSERT INTO '' + @CompPrd_Temp + '' (Memo1, CustomerCategory, ItemCode)
SELECT RouteMaster.Memo1, CustomerMaster.CustomerCategory, InvoiceDetail.ItemCode
FROM InvoiceDetail INNER JOIN InvoiceHeader ON InvoiceDetail.TransactionKey = InvoiceHeader.TransactionKey INNER JOIN HITEMGROUP ON InvoiceDetail.ItemCode = HITEMGROUP.HITEMCODE INNER JOIN RouteMaster INNER JOIN CustomerMaster ON RouteMaster.RouteCode = CustomerMaster.RouteCode ON InvoiceHeader.CustomerCode = CustomerMaster.CustomerCode
WHERE (InvoiceHeader.TransactionDate between '' + convert(smalldatetime,@MyFdate,102) + '' AND '' + convert(smalldatetime,@MyTdate,102) + '')
GROUP BY RouteMaster.Memo1, CustomerMaster.CustomerCategory, InvoiceDetail.ItemCode
ORDER BY RouteMaster.Memo1, CustomerMaster.CustomerCategory, InvoiceDetail.ItemCode''
exec sp_executesql @MySQL
print @MySQL
End
然后在执行它时:
exec SP_CompPrd ''##CompPrd_Temp160'',''01-01-2009'',''07-02-2010''
它为我返回此错误:
消息295，级别16，状态3，过程SP_CompPrd，第27行
语法错误将字符串转换为smalldatetime数据类型.
我需要在内存中的## temp表中构建逻辑，然后再填充此数据，这些数据是从逻辑中获取到数据集并从数据集中生成报告的.
那是我的逻辑.
顺便说一下，此逻辑在代码页后面的asp.net中运行，但我尝试在存储过程中进行，因为存储过程更好，更安全
我需要尽快提供帮助.
感谢所有
Then when execute it like :
exec SP_CompPrd ''##CompPrd_Temp160'',''01-01-2009'',''07-02-2010''
it return this error for me :
Msg 295, Level 16, State 3, Procedure SP_CompPrd, Line 27
Syntax error converting character string to smalldatetime data type.
What I need that I build logic in ##temp table in memory then later I will fill this data which I get it from the logic to dataset and product the report from dataset
That is my logic .
By the way this logic is run in asp.net behind code page but I try to make in stored procedure because stored procedure is better and safe
I need help ASAP .
Thanks for all
推荐答案
尝试执行以下操作:
try executing the following:
<br />
exec SP_CompPrd ''##CompPrd_Temp160'',''20090101'',''20100207''<br />
在SQL中使用日期的通用"格式:yyyyMMdd
Use the ''universal'' format for date''s in SQL: yyyyMMdd
我需要尽快获得帮助
永远不要把它放在您的消息中.海报的每个问题都是紧迫的，但这是一个免费的网站，人们有空的时候就会回答. (只是一个友好的提示)
Never good to put that in your message. Every question is urgent for the poster but this is a free site, people will answer when they have time for it. (just a friendly hint)
这篇关于语法错误将字符串转换为smalldatetime数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
