日期:2014-05-17  浏览次数:20510 次

给SQL添加月份参数!

改动都在注释里说明了!希望你帮我改下!
SQL code



----------Convert(nvarchar(7),t1.StateDate,120)as StateMonth 下面的SQL 把日期转换成月份 有点不安全,希望能改进

ALTER proc [dbo].[P_Prod_PoAndSalesMonth]  
(
--希望改成只能输入月份
  @datetime varchar(30)=''
)
 as 
 begin 
    declare @cur_datetime varchar (30)--本月初始化时间
    select @cur_datetime=case when @datetime = '' or @datetime is null then  CONVERT(datetime,CONVERT(char(8),getdate(),120)+'1')
    else CONVERT(datetime,CONVERT(char(8),cast(case when len(@datetime)=7 or len(@datetime)=6 then @datetime+'-01' 
    else @datetime end as datetime),120)+'1') end 
    
 
 create table #rep (Id  int identity(1,1),                 --序列号
  SkuNo                 varchar(60),                       --商品编号
  ProductName           varchar(100),                      --商品名称
  StateMonth            varchar(30),
  )

  --上月的数据汇总到临时表#t1  
  select t2.SkuNo,t2.ProductName,
  Convert(nvarchar(7),t1.StateDate,120)as StateMonth--新加了一个字段月份 
  into #t1 from Wms_StockDailyState as t1  left join V_Prod_Sku t2 on t1.SkuId=t2.SkuId
  where
  ----参数@datetime
  
  --这里希望月份大于上月初,小于本月
  --State>'2012-5 and state<'2012-7' 或者是State>'2012-05 and state<'2012-07'
  Convert(nvarchar(7),t1.StateDate,120)>'2012-5' and
  Convert(nvarchar(7),t1.StateDate,120)<'2012-7' 
  group by t2.SkuNo,t2.ProductName,Convert(nvarchar(7),StateDate,120)




    
 --本月的数据汇总到临时表#t2 
  select t2.SkuNo,t2.ProductName,Convert(nvarchar(7),t1.StateDate,120)as StateMonth
  
  into #t2 from Wms_StockDailyState as t1  left join V_Prod_Sku t2 on t1.SkuId=t2.SkuId
where 
--这里改成 大于 本月初 ,小于本月末
--也就是大于'2012-07'或者 2012-8
  --StateDate>=@cur_datetime  and StateDate<dateadd(month,1,@cur_datetime )
  --Convert(nvarchar(7),t1.StateDate,120)=@datetime
  group by t2.SkuNo,t2.ProductName,Convert(nvarchar(7),t1.StateDate,120)

 select * from #rep
 end