sql: 生日三个月内有效

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

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