日期:2014-05-18  浏览次数:20574 次

存储过程,日期问题
下面是存储过程,我想选取date_qi到date_zhi之间的数据
数据库中日期存储的就是datetime型的
日期能够直接比较吗?

create     proc   test8
@vproject_id   varchar(30),
@vpartner_id   varchar(30),
@date_qi   datetime,
@date_zhi   datetime,
                @vyeji_return   real=0.0   output
as
declare   @temp1   varchar(30)
declare   my_cursor   cursor   for  
select   income_id
from   project_income_table
where   project_id=@vproject_id
open   my_cursor
fetch   next   from   my_cursor   into   @temp1
while   @@fetch_status=0
begin
declare   @vyeji_temp   real
select   @vyeji_temp=sum(income_value)    
from     income_value_table          
where     income_id=@temp1
                and   partner_id=@vpartner_id
and   income_date   between   @date_qi   and   @date_zhi--这一句有问题,date_qi给2007-5-1,date_zhi给2007-5-3,查不出数据,如果不用参数,直接写‘2007-5-1’和‘2007-5-3’,没问题
set   @vyeji_return   =   @vyeji_return   +   @vyeji_temp

fetch   next   from   my_cursor   into   @temp1
end
close   my_cursor
deallocate   my_cursor


------解决方案--------------------
存儲過程沒有看到問題,你執行的代碼是怎樣的?

時間的前後要加上單引號的。

eg:


Declare @vyeji_return real
EXEC test8 '1111 ', '2222 ', '2007-5-1 ', '2007-5-3 ', @vyeji_return Output
------解决方案--------------------
begin
declare @vyeji_temp real
select @vyeji_temp=sum(income_value)
from income_value_table
where income_id=@temp1
and partner_id=@vpartner_id
and income_date between @date_qi and @date_zhi
建议将以上部分改成:

begin
declare @vyeji_temp real
EXEC( "select @vyeji_temp=sum(income_value)
from income_value_table
where income_id= "+ @temp1
+ "and partner_id= "+ @vpartner_id + "
and income_date between "+ @date_qi + " and "+ @date_zhi)