存储过程参数 datetime 类型问题

[jiangqinggao]

DateTime date1 = Convert.ToDateTime(this.dateTimePicker1.Text.ToString());
DateTime date2 = Convert.ToDateTime(this.dateTimePicker2.Text.ToString()).AddDays(+1);

exec sp_mm_lg_stockday '2009-9-2 0:00:00' , '2009-9-3 0:00:00' ,'100000000',28

存储过程 带四个参数 分别是 date1 date2 datetime 类型。 后面两个是 字符和整形

在查询分析器老是报错。 从字符串转换为 datetime 时发生语法错误。

那么如何避免这个问题。。。。 要疯了。

[htl258_Tony]

这是你过程代码里面有问题,需要修改存储过程代码.

[华夏小卒]

exec sp_mm_lg_stockday '2009-09-02 00:00:00' , '2009-09-03 00:00:00' ,'100000000',28 

[lihan6415151528]

cast('2009-9-2 0:00:00' as datetime)

[SQL77]

DateTime date1 = Convert.ToDateTime(this.dateTimePicker1.Text.ToString());
DateTime date2 = Convert.ToDateTime(this.dateTimePicker2.Text.ToString()).AddDays(+1);

这两个C#日期的格式和数据库中的格式好像不同!!!!!

[dawugui]

exec sp_mm_lg_stockday '2009-09-02 00:00:00' , '2009-09-03 00:00:00' ,'100000000',28

[--小F--]

[Quote=引用 5 楼 dawugui 的回复:]
exec sp_mm_lg_stockday '2009-09-02 00:00:00' , '2009-09-03 00:00:00' ,'100000000',28
[/Quote]

支持乌龟

[dawugui]

exec sp_mm_lg_stockday '2009-09-02 00:00:00' , '2009-09-03 00:00:00' ,'100000000',28

exec sp_mm_lg_stockday cast('2009-09-02 00:00:00' as datetime), cast('2009-09-03 00:00:00' as datetime),'100000000',28

[htl258_Tony]

[Quote=引用 7 楼 dawugui 的回复:]
exec sp_mm_lg_stockday '2009-09-02 00:00:00' , '2009-09-03 00:00:00' ,'100000000',28

exec sp_mm_lg_stockday cast('2009-09-02 00:00:00' as datetime), cast('2009-09-03 00:00:00' as datetime),'100000000',28
[/Quote]
应该不是这个问题.只要格式合法的,会自动转换的.而且过程后面不支持函数.

[gw6328]

学习

[SQL77]

程序里面的日期格式是不同的

[jiangqinggao]

晕了。。存储过程里面一个 列名 'FendQty' 无效。
临时表 都建立了 查临时表结构都有。。。怎么会无效？

[华夏小卒]

存储过程代码贴出来，

是不是误写错了？

[jiangqinggao]

看到了。 哎。

[jiangqinggao]

CREATE procedure sp_mm_lg_stockday
(
@startdatetime datetime,
@enddatetime datetime,
@Unicode varchar(256),
@storeID int

) as
begin
/**************************
*
存储过程名称:sp_mm_lg_stockday
功能描述:库存收发存汇总表
参 数:
* 创建人：by lg 2003-5-12
* 版本号：1.01
*
* 修改记录：
****************************/
set nocount on
/****************************
上日结存数量算法:启用期间期初数量+启用日至开始日期
****************************/
--declare @s varchar(50)
--set @s = '2008-11-28'


create table #lg_happenPre(
FItemID int Null,
FStockID int Null,
FBegQty int Default (0), --期初结存
FInQty int Default (0), --入库数量
FtuikuQty int Default (0), --退库数量
FOutQty int Default (0), --出库数量
FtuihuoQty int Default (0), --退货数量
FpanyingQty int Default (0), --盘盈数量
FpankuiQty int Default (0), --盘亏数量
FyikuQty int Default (0) --移库数量
)

insert into #lg_happenPre (FStockID, FItemID,FBegQty) -- 期初数据插入临时表s
select
t3.StoreID,a.UniCode,isnull(a.Quantity,0)
from JS_StorelistTbl a,JS_SysItemTbl b,JS_InStoTbl c,JS_SupplyTbl d ,JS_StoreTbl t3
where a.UniCode =b.UniCode and a.Quantity > 0 and c.Supplyid =d.SuppID
and c.storeID=t3.storeID
and c.opttime <= @startdatetime order by A.unicode


insert into #lg_happenPre (FStockID, FItemID,FInQty) -- 入库数插入临时表
select t4.StoreID, v1.UniCode,isnull(v1.Quantity,0)
from JS_InListTbl v1,JS_SysItemTbl t2,JS_StoreTbl t3 ,JS_InStoTbl t4
where v1.UniCode = t2.unicode
and t4.StoreID=t3.StoreID
and t4.billnum =v1.billnum
and t4.perftype =0
and t4.OptTime between @startdatetime and @enddatetime
and t4.StoreID =@storeID and v1.UniCode =@Unicode


insert into #lg_happenPre (FStockID, FItemID,FtuikuQty) --退库数插入临时表
select t4.StoreID, v1.UniCode,isnull(v1.Quantity,0)
from JS_InListTbl v1,JS_SysItemTbl t2,JS_StoreTbl t3 ,JS_InStoTbl t4
where v1.UniCode = t2.unicode
and t4.StoreID=t3.StoreID
and t4.billnum =v1.billnum
and t4.perftype =7
and t4.OptTime between @startdatetime and @enddatetime
and t4.StoreID =@storeID and v1.UniCode =@Unicode


insert into #lg_happenPre (FStockID, FItemID,FOutQty) --出库数插入临时表
select t4.StoreID, v1.UniCode,isnull(v1.Quantity,0)
from JS_OutListTbl v1,JS_SysItemTbl t2,JS_StoreTbl t3 ,JS_OutStoTbl t4
where v1.UniCode = t2.unicode
and t4.StoreID=t3.StoreID
and t4.billnum =v1.billnum
and t4.perftype =5
and t4.OptTime between @startdatetime and @enddatetime
and t4.StoreID =@storeID and v1.UniCode =@Unicode

insert into #lg_happenPre (FStockID, FItemID,FtuihuoQty) --退货数插入临时表
select t4.StoreID, v1.UniCode,isnull(v1.Quantity,0)
from JS_OutListTbl v1,JS_SysItemTbl t2,JS_StoreTbl t3 ,JS_OutStoTbl t4
where v1.UniCode = t2.unicode
and t4.StoreID=t3.StoreID
and t4.billnum =v1.billnum
and t4.perftype =6
and t4.OptTime between @startdatetime and @enddatetime
and t4.StoreID =@storeID and v1.UniCode =@Unicode

insert into #lg_happenPre (FStockID, FItemID,FpanyingQty) --盘盈数插入临时表
select t4.StoreID, v1.UniCode,isnull(v1.Quantity,0)
from JS_PerflistTb v1,JS_SysItemTbl t2,JS_StoreTbl t3 ,JS_PerfTbl t4
where v1.UniCode = t2.unicode
and t4.StoreID=t3.StoreID
and t4.billnum =v1.billnum
and t4.perftype =3
and t4.OptTime between @startdatetime and @enddatetime
and t4.StoreID =@storeID and v1.UniCode =@Unicode

insert into #lg_happenPre (FStockID, FItemID,FpanyingQty) --盘亏数插入临时表
select t4.StoreID, v1.UniCode,isnull(v1.Quantity,0)
from JS_PerflistTb v1,JS_SysItemTbl t2,JS_StoreTbl t3 ,JS_PerfTbl t4
where v1.UniCode = t2.unicode
and t4.StoreID=t3.StoreID
and t4.billnum =v1.billnum
and t4.perftype =4
and t4.OptTime between @startdatetime and @enddatetime
and t4.StoreID =@storeID and v1.UniCode =@Unicode


insert into #lg_happenPre (FStockID, FItemID,FyikuQty) --移库数插入临时表
select t4.StoreID, v1.UniCode,isnull(v1.Quantity,0)
from JS_InListTbl v1,JS_SysItemTbl t2,JS_StoreTbl t3 ,JS_InStoTbl t4
where v1.UniCode = t2.unicode
and t4.StoreID=t3.StoreID
and t4.billnum =v1.billnum
and t4.perftype =9
and t4.OptTime between @startdatetime and @enddatetime
and t4.StoreID =@storeID and v1.UniCode =@Unicode


create table #lg_happen(
FItemID int Null,
FStockID int Null,
FBegQty int Default (0), --期初结存
FInQty int Default (0), --入库数量
FtuikuQty int Default (0), --出库数量
FOutQty int Default (0), --出库数量
FtuihuoQty int Default (0), --出库数量
FpanyingQty int Default (0), --出库数量
FpankuiQty int Default (0), --出库数量
FyikuQty int Default (0) --出库数量
)

insert into #lg_happen (FStockID, FItemID,FBegQty) --期初
select FStockID, FItemID,sum(FBegQty)
from #lg_happenPre
group by FStockID,FItemID

insert into #lg_happen (FStockID, FItemID,FInQty) --入库
select FStockID, FItemID,sum(FInQty)
from #lg_happenPre
group by FStockID,FItemID

insert into #lg_happen (FStockID, FItemID,FtuikuQty) --退库
select FStockID, FItemID,sum(FtuikuQty)
from #lg_happenPre
group by FStockID,FItemID

insert into #lg_happen (FStockID, FItemID,FOutQty) --出库
select FStockID, FItemID,sum(FOutQty)
from #lg_happenPre
group by FStockID,FItemID


insert into #lg_happen (FStockID, FItemID,FtuihuoQty) --退货
select FStockID, FItemID,sum(FtuihuoQty)
from #lg_happenPre
group by FStockID,FItemID


insert into #lg_happen (FStockID, FItemID,FpanyingQty) --盘盈
select FStockID, FItemID,sum(FpanyingQty)
from #lg_happenPre
group by FStockID,FItemID


insert into #lg_happen (FStockID, FItemID,FpankuiQty) --盘亏
select FStockID, FItemID,sum(FpankuiQty)
from #lg_happenPre
group by FStockID,FItemID


insert into #lg_happen (FStockID, FItemID,FyikuQty) --移库
select FStockID, FItemID,sum(FyikuQty)
from #lg_happenPre
group by FStockID,FItemID


----
--部分字段移出
-----


create table #lg_data(

FItemID int Null,
FStockID int Null,
FBegQty int Default (0), --期初结存
FInQty int Default (0), --入库数量
FtuikuQty int Default (0), --出库数量
FOutQty int Default (0), --出库数量
FtuihuoQty int Default (0), --出库数量
FpanyingQty int Default (0), --出库数量
FpankuiQty int Default (0), --出库数量
FyikuQty int Default (0), --出库数量
FendQty int Default (0) --出库数量
)
insert into #lg_data(FStockID, FItemID,FBegQty ,FInQty,FtuikuQty,FOutQty,FtuihuoQty,FpanyingQty,FpankuiQty,FyikuQty,FendQty)
select FStockID, FItemID,sum(FBegQty) ,sum(FInQty),sum(FtuikuQty),sum(FOutQty) ,sum(FtuihuoQty),sum(FpanyingQty),sum(FpankuiQty),sum(FyikuQty),
sum(FBegQty) + sum(FInQty) + sum(FtuikuQty) - sum(FOutQty) - sum(FtuihuoQty) + sum(FpanyingQty) - sum(FpankuiQty) + sum(FyikuQty)
from #lg_happen
group by FStockID, FItemID

--update t1 set t1.FNumber=t2.FNumber,t1.Fname=t2.Fname,t1.Fmodel=t2.Fmodel from #lg_data t1 inner join t_icitem t2 on t1.fitemid=t2.fitemid --
--update t1 set t1.FunitName=t3.Fname from #lg_data t1 inner join (t_icitem t2 inner join t_measureunit t3 on t2.funitid=t3.fmeasureunitid) on t1.fitemid=t2.fitemid
--update t1 set t1.FstockName=t2.fname from #lg_data t1 inner join t_stock t2 on t1.fstockid=t2.fitemid
--update t1 set t1.FStockPlacenumber=t2.fnumber,t1.FStockPlaceName=t2.fname from #lg_data t1 inner join t_stockplace t2 on t1.FStockPlaceID=t2.fspid

insert into #lg_data(FItemID,FBegQty ,FInQty,FtuikuQty,FOutQty,FtuihuoQty,FpanyingQty,FpankuiQty,FyikuQty,FendQty)
select '合计',sum(FBegQty) ,sum(FInQty),sum(FtuikuQty),sum(FOutQty),sum(FtuihuoQty),sum(FpanyingQty),sum(FpankuiQty),sum(FyikuQty),
sum(FBegQty) + sum(FInQty) + sum(FtuikuQty) - sum(FOutQty) - sum(FtuihuoQty) + sum(FpanyingQty) - sum(FpankuiQty) + sum(FyikuQty)
from #lg_happen

--update t1 set t1.FendQty=t1.FBegQty+t1.FInQty-t1.FOutQty from #lg_data t1


select
t1.FStockID 仓库id,
t1.FItemID 零件号 ,
t1.FBegQty 期初数 ,
t1.FInQty 入库数 ,
t1.FtuikuQty 退库数 ,
t1.FOutQty 出库数 ,
t1.FtuihuoQty 退货数 ,
t1.FpanyingQty 盘盈数 ,
t1.FpankuiQty 盘亏数,
t1.FyikuQty 移库数,
t1.FendQty 结存数量
from #lg_data t1
where t1.FBegQty<>0 or t1.FInQty<>0 or t1.FOutQty<>0 or t1.FtuikuQty <>0 or t1.FtuihuoQty <> 0 or t1.FpankuiQty <> 0 or t1.FyikuQty <>0 or t1.FendQty <> 0
order by t1.FItemID

drop table #lg_happenPre
drop table #lg_happen
drop table #lg_data

end
GO

仓库收发存汇总。很麻烦的。

[feixianxxx]

[Quote=引用 11 楼 jiangqinggao 的回复:]
晕了。。存储过程里面一个 列名 'FendQty' 无效。
临时表 都建立了  查临时表结构都有。。。怎么会无效？
[/Quote]
你在哪里查询临时表？
临时表建哪了？