sql: 生日三个月内有效

DECLARE @birthday  datetime,@stat datetime,@end datetime,@statbirthday datetime,@endbirthday datetime,@thirdbirthday datetime,@firthbirthday datetime, @year int,@month int , @day int,@str varchar(20),@total int,@firthmonth int,@thirmonth int,@now  datetime,@vipno nvarchar(10) --参数    
set @vipno=''8888888''   
set @now=''2014-01-25''   
set @year=Year(@now)    
select  @birthday=birthday from vip1 where vipno=@vipno    
set @month=month(@birthday)    
set @day=day(@birthday)    
set @str=cast(@year as varchar(4))+''-''+cast(@month as varchar(2))+''-''+cast(@day as varchar(2))    
set @birthday=cast(@str as datetime)    
--會員生日有效生首日起至3個月內有效    
set @firthbirthday=DATEADD(mm, DATEDIFF(mm,0,@birthday)-2, 0)--DATEADD(mm, DATEDIFF(mm,0,@birthday), 0)  --当月的第一天    
--set @thirdbirthday=DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+3, 0)) --后三个月最后的一天    
SELECT @firthbirthday=DATEADD(MONTH,-2,DATEADD(MONTH,DATEDIFF(MONTH,0,@birthday),0))    
--select cast(@firthbirthday as datetime)    
--set @firthbirthday=DATEADD(MONTH,-2,DATEADD(MONTH,DATEDIFF(MONTH,0,@birthday),0))--DATEADD(Month,0,CONVERT(datetime,CONVERT(char(8),getdate(),120)+''1''))    
--select @firthbirthday    
--DATEADD(mm,0, DATEDIFF(mm,0,@birthday), 0)  --前三个月的第一天    
--set @firthbirthday=convert(datetime, @thirdbirthday)    
--SELECT  三個月有效 塗聚文 涂聚文    
set @firthmonth=month(@now)-2    
set @thirmonth=month(@now)    
set @thirdbirthday=DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@now)+1, 0)) --当月最后的一天    
set @thirdbirthday=cast(@thirdbirthday as datetime)    
set @statbirthday=DATEADD(mm, DATEDIFF(mm,0,@birthday)-13, 0)    
set @endbirthday=DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@birthday)-1, 0))    
--select @statbirthday,@endbirthday,@firthbirthday,@thirdbirthday,@birthday,@firthmonth,@thirmonth    
--以月份來吧    
SELECT  *  FROM View_birthdayVipdlyList WHERE DATEDIFF(month,NowBirthday,@now)>=0 and  DATEDIFF(month,NowBirthday,@now)<=2  and vipno=@vipno order by VipBirthdayDate desc   
   
--SELECT  *  FROM vip1 WHERE indate>@statbirthday AND  indate<@endbirthday and  month(birthday)>=@firthmonth and month(birthday)<=@thirmonth --and vipno=@vipno    
   
--SELECT @total=ISNULL(SUM(amount),0) FROM View_birthdayVipdlyList WHERE indate>@statbirthday AND  indate<@endbirthday and  month(birthday)>=@firthmonth and month(birthday)<=@thirmonth and vipno=@vipno    
--SELECT @total=@total*.01     
   
   
   
SELECT  count(*) as ''total''  FROM View_birthdayVipdlyList WHERE DATEDIFF(month,NowBirthday,getdate())>=0 and  DATEDIFF(month,NowBirthday,getdate())<=2  and vipno=@vipno    
   
   
if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[GetVipNowBirthday]'') and xtype in (N''FN'', N''IF'', N''TF''))    
drop function [dbo].[GetVipNowBirthday]    
GO    
CREATE   function  GetVipNowBirthday    
(      
   @vipno nvarchar(10), --参数    
   @now datetime    
)    
returns datetime    
AS   
begin   
declare @NowBirthday datetime, @birthday  datetime,@stat datetime,@end datetime,@statbirthday datetime,@endbirthday datetime,@thirdbirthday datetime,@firthbirthday datetime, @year int,@month int , @day int,@str varchar(40),@total decimal   
   
select @birthday=birthday from vip1 where vipno=@vipno and birthday is not null   
set @year=Year(@now)    
if month(@now)=1 and month(@birthday)=12    
begin   
   set @year=@year-1    
end   
if month(@now)=1 and month(@birthday)=11    
begin   
   set @year=@year-1    
end   
if month(@now)=2 and month(@birthday)=12     
begin   
   set @year=@year-1    
end   
set @month=month(@birthday)    
set @day=day(@birthday)    
set @str=cast(@year as varchar(4))+''-''+cast(@month as varchar(2))+''-''+cast(@day as varchar(2))    
set @NowBirthday=convert(datetime,@str) ---cast(@str as datetime)    
return @NowBirthday    
end   
GO    
   
select top 100  vipno,birthday,[dbo].[GetVipNowBirthday](vipno,''2014-03-23'') as ''now'' from dbo.vip1 where month(birthday)=12 or month(birthday)=11    

转自:http://www.16aspx.com/Article/3906