我有存储过程。在它的主体中，我选择了一些数据，其中一个字段是nvarchar，但它包含日期（前18个符号只是文本，但随后是日期）。然后我使用那个日期和另一个日期（已经是真实日期）比较的条件。
Select (bla bla) from (bla bla)
where DATEDIFF(DAY,DueDateDay,CAST(SUBSTRING(myField,18,10) as datetime)) < @dayCount
在SQL Server Management Studio中，此SP运行良好。但是当我使用SqlCommand.ExecuteReader()从我的.NET应用程序调用这个SP时，它会抛出一个异常：
nvarchar数据类型到日期时间数据类型的转换导致值超出范围。
为什么？？
SP:
ALTER PROCEDURE [dbo].[UK_GetPlanningDetails_test]
@itemStart nvarchar(30),
@itemEnd nvarchar(30),
@plan_codeStart nvarchar(3),
@plan_codeEnd nvarchar(3),
@arrivalDelay bit,
@demandDelay bit,
@negativeCount bit,
@arrivalMoveTo bit,
@arrivalMoveFrom bit,
@dayCount int,
@noNeedArrival bit,
@needAll bit,
@negativeOnSKZ bit = 0
AS
BEGIN
declare @sql nvarchar(2000);
declare @sql2 nvarchar(2000);
create table #temp
(
Item nvarchar(30),
DueDateDay datetime,
description nvarchar(40),
plan_code nvarchar(3),
QtyOnHand nvarchar(40),
QtyReq nvarchar(40),
QtyRecv nvarchar(40),
ExceptMessage nvarchar(2800),
MrpRef nvarchar(2800),
LastChangedDate datetime
)
create table #temp2
(
Item nvarchar(30),
DueDateDay datetime,
description nvarchar(40),
plan_code nvarchar(3),
QtyOnHand nvarchar(40),
QtyReq nvarchar(40),
QtyRecv nvarchar(40),
ExceptMessage nvarchar(2800),
MrpRef nvarchar(2800),
LastChangedDate datetime
)
set @sql = N'SELECT o.Item, isnull(o.DueDateDay,''1950-01-01 00:00:00.000''), ISNULL(o.description,''''), isnull(o.plan_code,''''), convert(nvarchar(40),o.QtyOnHand), convert(nvarchar(40),o.QtyReq), convert(nvarchar(40),o.QtyRecv),isnull(o.ExceptMessage,''''), isnull(o.MrpRef,''''), isnull(o.LastChangedDate,''1950-01-01 00:00:00.000'')
FROM [std_app].[dbo].[PlanningDetailsView] i
left join [std_app].[dbo].[PlanningDetailsView] o on i.[Item]=o.[Item]
where (i.ExceptMessage is not null)'
if (@plan_codeStart<>'') set @sql=@sql+ ' and (i.[plan_code] >= '''+ @plan_codeStart+''')'
if (@plan_codeEnd<>'') set @sql=@sql+ ' and (i.[plan_code] <= '''+ @plan_codeEnd+''')'
if (@itemStart<>'') set @sql=@sql+ ' and (i.[Item] >= '''+ @itemStart+''')'
if (@itemEnd<>'') set @sql=@sql+ ' and (i.[Item] <= '''+ @itemEnd+''')'
if (@needAll<>1)
begin
if(@negativeOnSKZ=0) set @sql = @sql + ' and (i.MrpRef not like (''TPLN XFD-PLANT%'')) '
set @sql =@sql + 'and ('
if(@arrivalDelay<>0) set @sql = @sql + '(i.ExceptMessage like (''Задержка прогнозн.прихода%'')) or '
if(@demandDelay<>0) set @sql = @sql + '(i.ExceptMessage like (''Задержка прогнозн.потребн.%'')) or '
if(@negativeCount<>0) set @sql = @sql + '(i.ExceptMessage like (''Начал.колич.в наличии отрицательно%'')) or '
if(@arrivalMoveTo<>0) set @sql = @sql + '(i.ExceptMessage like (''Перем.В Приход%'')) or '
if(@noNeedArrival<>0) set @sql = @sql + '(i.ExceptMessage like (''Приход не требуется%'')) or '
if(@negativeOnSKZ<>0) set @sql = @sql + '(i.MrpRef like (''TPLN XFD-PLANT%'')) or '
set @sql =@sql + '(i.ExceptMessage like('''')))'
if(@arrivalMoveFrom<>0)
begin
set @sql2 = N'SELECT Item, isnull(DueDateDay,''1950-01-01 00:00:00.000''), ISNULL(description,''''), isnull(plan_code,''''), convert(nvarchar(40),QtyOnHand), convert(nvarchar(40),QtyReq), convert(nvarchar(40),QtyRecv),isnull(ExceptMessage,''''), isnull(MrpRef,''''), isnull(LastChangedDate,''1950-01-01 00:00:00.000'')
FROM [std_app].[dbo].[PlanningDetailsView]
where (ExceptMessage is not null)
and (ExceptMessage like (''Перем.из прихода%''))'
if (@plan_codeStart<>'') set @sql2=@sql2+ ' and ([plan_code] >= '''+ @plan_codeStart+''')'
if (@plan_codeEnd<>'') set @sql2=@sql2+ ' and ([plan_code] <= '''+ @plan_codeEnd+''')'
if (@itemStart<>'') set @sql2=@sql2+ ' and ([Item] >= '''+ @itemStart+''')'
if (@itemEnd<>'') set @sql2=@sql2+ ' and ([Item] <= '''+ @itemEnd+''')'
insert into #temp2 execute(@sql2)
if(@dayCount>0)
begin
delete from #temp2 where DATEDIFF(DAY,DueDateDay,CAST(SUBSTRING(ExceptMessage,18,10) as datetime))<@dayCount
end
end
end
insert into #temp execute(@sql)
select my.*, StartDate = isnull(their.StartDate,'1950-01-01 00:00:00.000') from (select Item, DueDateDay, description, plan_code, QtyOnHand, QtyReq, QtyRecv, ExceptMessage, MrpRef, LastChangedDate from #temp
union
(
select o.Item, o.DueDate, o.description, o.plan_code, o.QtyOnHand, o.QtyReq, o.QtyRecv, o.ExceptMessage, o.MrpRef, o.LastChangedDate
from #temp2 i left join [std_App].dbo.PlanningDetailsView o on i.Item = o.Item
)) my
left join ( select
--- Four part key -----------------------
--- Check performance before changing ---
apsplan.item as Item
, apsplan.due_date_day as DueDateDay
, 0 as QtyOnHand
, case when is_demand = 1 then apsplan.qty else 0 end as QtyReq
, case when is_demand = 1 then 0 else apsplan.qty end as QtyRecv
, case
when is_demand = 1
then case
when apsplan.ref_type = 'S'
then dbo.PPSRef(apsplan.ref_num, isnull(apsplan.ref_line_suf,0))
else
case
WHEN apsplan.xref_num IS NOT NULL
THEN 'X'
ELSE ''
END +
CASE apsplan.ref_type
WHEN 'N' THEN 'PPLN '
WHEN 'D' THEN 'TPLN '
WHEN 'J' THEN 'PJOB '
WHEN 'M' THEN 'PMPS '
WHEN 'S' THEN 'PPS '
WHEN 'C' THEN 'CO '
ELSE ''
END +
CASE
WHEN apsplan.ref_num IS NOT NULL
THEN apsplan.ref_num
ELSE ''
END +
CASE
WHEN apsplan.ref_line_suf IS NOT NULL
THEN '-' + CAST(apsplan.ref_line_suf AS CHAR(4))
ELSE ''
END +
CASE
WHEN apsplan.ref_rel IS NOT NULL
THEN '-' + SUBSTRING('000' + CAST(apsplan.ref_rel AS NVARCHAR),
LEN('000' + CAST(apsplan.ref_rel AS NVARCHAR)) -3, 4)
ELSE ''
END
end
else 'PLN ' + apsplan.ref_num -- ORDER000.ORDERID
end
as MrpRef
, NULL as MrpExcept --dbo.GetMrpExcMesg(apsplan.item, apsplan.ref_type, apsplan.ref_num, apsplan.ref_line_suf, apsplan.ref_rel)
, apsplan.start_date as StartDate
FROM (
select *
,due_date as EndDate
,ISNULL((select mp.ENDDATE
from apsplandetail
join MATLPLAN000 mp on mp.MATLTAG = apsplandetail.matltag and mp.PMATLTAG = 0
join ORDER000 o on o.ORDERID = mp.ORDERID and o.ORDTYPE = 310 -- Safety stock
where apsplan.RowPointer = apsplandetail.apsplan_reference), apsplan.due_date)
as ProjectedDate
from apsplan with(index(IX_apsplan_item_due_date_day))
where apsplan.is_demand = 1 and apsplan.qty != 0
union all
select *
,CASE WHEN SUBSTRING(apsplan.ref_num,1,3) = 'ROP' THEN apsplan.due_date
ELSE ISNULL ((select mp.NEEDDATE
from MATLPLAN000 mp
join ORDER000 o on o.ORDERID = mp.ORDERID and o.ORDTYPE = 310 -- Safety stock
where mp.MATLTAG = apsplan.ref_num and mp.PMATLTAG = 0), apsplan.due_date)
END as EndDate
,due_date as ProjectedDate
from apsplan with(index(IX_apsplan_item_due_date_day))
where apsplan.is_demand = 0 and apsplan.ref_type <> 'Z'
) apsplan) their on their.Item=my.Item and my.MrpRef=their.MrpRef
where my.MrpRef not like case when @negativeOnSKZ=0 then ('TPLN XFD-PLANT%') else ('some unreal data') end or my.MrpRef like case when @negativeOnSKZ=1 then ('TPLN XFD-PLANT%') else ('some unreal data') end
order by my.item,my.DueDateDay
END
呼叫：
public static DataTable getTable(string connectionString, string SQLcommand)
{
SqlConnection Con = new SqlConnection(connectionString);
Con.Open();
SqlCommand cmd1 = new SqlCommand(SQLcommand, Con);
cmd1.CommandTimeout = 5 * 60 * 1000;
SqlDataReader Read1 = cmd1.ExecuteReader();
DataTable getTab = new DataTable();
getTab.Load(Read1);
Con.Close();
return getTab;
}
SQLcommand = EXEC [dbo].[UK_GetPlanningDetails_test] @itemStart = N'', @itemEnd = N'', @plan_codeStart = N'', @plan_codeEnd = N'', @arrivalDelay = 0 , @demandDelay = 0 , @negativeCount = 0 , @arrivalMoveTo = 0 , @arrivalMoveFrom = 1 , @dayCount = 10 , @noNeedArrival = 0 , @needAll = 0 , @negativeOnSKZ = 0
我认为字符串中的日期格式不正确。在SQLServerManagementStudio中工作而在.Net中不工作的原因可能是连接语言。
第一个选择有效，但第二个给出了相同的选择
SET LANGUAGE us_english
select CAST('12.18.2012' as datetime)
SET LANGUAGE turkish
select CAST('12.18.2012' as datetime)