存储过程参数 datetime 类型问题

jiangqinggao 2009-09-02 03:16:43
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 时发生语法错误。

那么如何避免这个问题。。。。 要疯了。
...全文
217 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
feixianxxx 2009-09-02
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 jiangqinggao 的回复:]
晕了。。存储过程里面一个 列名 'FendQty' 无效。
临时表 都建立了  查临时表结构都有。。。怎么会无效?
[/Quote]
你在哪里查询临时表?
临时表建哪了?
jiangqinggao 2009-09-02
  • 打赏
  • 举报
回复
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

仓库收发存汇总。很麻烦的。
jiangqinggao 2009-09-02
  • 打赏
  • 举报
回复
看到了。 哎。
华夏小卒 2009-09-02
  • 打赏
  • 举报
回复
存储过程代码贴出来,

是不是误写错了?
jiangqinggao 2009-09-02
  • 打赏
  • 举报
回复
晕了。。存储过程里面一个 列名 'FendQty' 无效。
临时表 都建立了 查临时表结构都有。。。怎么会无效?
SQL77 2009-09-02
  • 打赏
  • 举报
回复
程序里面的日期格式是不同的
gw6328 2009-09-02
  • 打赏
  • 举报
回复
学习
htl258_Tony 2009-09-02
  • 打赏
  • 举报
回复
[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]
应该不是这个问题.只要格式合法的,会自动转换的.而且过程后面不支持函数.
dawugui 2009-09-02
  • 打赏
  • 举报
回复
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
--小F-- 2009-09-02
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 dawugui 的回复:]
exec sp_mm_lg_stockday '2009-09-02 00:00:00' , '2009-09-03 00:00:00' ,'100000000',28
[/Quote]

支持乌龟
dawugui 2009-09-02
  • 打赏
  • 举报
回复
exec sp_mm_lg_stockday '2009-09-02 00:00:00' , '2009-09-03 00:00:00' ,'100000000',28
SQL77 2009-09-02
  • 打赏
  • 举报
回复
DateTime date1 = Convert.ToDateTime(this.dateTimePicker1.Text.ToString());
DateTime date2 = Convert.ToDateTime(this.dateTimePicker2.Text.ToString()).AddDays(+1);

这两个C#日期的格式和数据库中的格式好像不同!!!!!
lihan6415151528 2009-09-02
  • 打赏
  • 举报
回复
cast('2009-9-2 0:00:00' as datetime)
华夏小卒 2009-09-02
  • 打赏
  • 举报
回复

exec sp_mm_lg_stockday '2009-09-02 00:00:00' , '2009-09-03 00:00:00' ,'100000000',28
htl258_Tony 2009-09-02
  • 打赏
  • 举报
回复
这是你过程代码里面有问题,需要修改存储过程代码.

22,302

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧