--简体繁体注意改下 create table T(id char(02),type nvarchar(10),where1 nvarchar(30), where2 nvarchar(30)) insert into T select 'N1','星期','1,2,3,4,5','' union all select 'B2','每月','1,2,3,4,5','' union all select 'C3','隔月','1,2,3,4,5','1,2,3,4,5' union all select 'D4','單數周','1,2,3,4,5','' union all select 'E5','雙月周','1,2,3,4,5',''GOdeclare @input datetime set @input ='2009-06-04' declare @w1 int, @w2 int,@w3_1 int, @w3_2 int, @w4 int,@w5 int --星期, 默認星期天是1,星期一是2...類推 select @w1=datepart(weekday, @input) -1 --每月 select @w2=day(@input) --隔月 select @w3_1=month(@input), @w3_2=day(@input) --單數周 select @w4= ( datepart(week,@input) - datepart(week, convert(datetime, convert(char(07),@input,120)+'-01'))+1)%2 --雙月周 select @w5=month(@input)%2 select * from T where (type=N'星期' and charindex(rtrim(@w1) ,where1)>0) or (type=N'每月' and charindex(','+rtrim(@w2)+',' , ','+rtrim(where1)+',')>0) or (type=N'隔月' and charindex(','+rtrim(@w3_1)+',' , ','+rtrim(where1)+',')>0 and charindex(','+rtrim(@w3_2)+',' , ','+rtrim(where2)+',')>0 ) or (type=N'單數周' and @w4=1 and charindex(rtrim(@w1) ,where1)>0 ) or (type=N'雙月周' and @w5=0 and charindex(rtrim(@w1) ,where1)>0 )GO drop table T
N1 星期 1,2,3,4,5//星期一,
B2 每月 1,2,3,4,5//每月1号
C3 隔月 1,2,3,4,5 1,2,3,4,5//1月1号,1月2号,同理
D4 单数周 1,2,3,4,5 //星期一
E5 双月周 1,2,3,4,5 //星期一
N1 星期 1,2,3,4,5//星期一,
B2 每月 1,2,3,4,5//每月1号
C3 隔月 1,2,3,4,5 1,2,3,4,5//1月1号,1月2号,同理
D4 单数周 1,2,3,4,5 //星期一
E5 双月周 1,2,3,4,5 //星期一
--简体繁体注意改下
create table T(id char(02),type nvarchar(10),where1 nvarchar(30), where2 nvarchar(30))
insert into T
select 'N1','星期','1,2,3,4,5',''
union all select 'B2','每月','1,2,3,4,5',''
union all select 'C3','隔月','1,2,3,4,5','1,2,3,4,5'
union all select 'D4','單數周','1,2,3,4,5',''
union all select 'E5','雙月周','1,2,3,4,5',''GOdeclare @input datetime
set @input ='2009-06-04'
declare @w1 int, @w2 int,@w3_1 int, @w3_2 int, @w4 int,@w5 int
--星期, 默認星期天是1,星期一是2...類推
select @w1=datepart(weekday, @input) -1
--每月
select @w2=day(@input)
--隔月
select @w3_1=month(@input), @w3_2=day(@input)
--單數周
select @w4= ( datepart(week,@input) - datepart(week, convert(datetime, convert(char(07),@input,120)+'-01'))+1)%2
--雙月周
select @w5=month(@input)%2 select * from T
where (type=N'星期' and charindex(rtrim(@w1) ,where1)>0)
or (type=N'每月' and charindex(','+rtrim(@w2)+',' , ','+rtrim(where1)+',')>0)
or (type=N'隔月' and charindex(','+rtrim(@w3_1)+',' , ','+rtrim(where1)+',')>0 and charindex(','+rtrim(@w3_2)+',' , ','+rtrim(where2)+',')>0 )
or (type=N'單數周' and @w4=1 and charindex(rtrim(@w1) ,where1)>0 )
or (type=N'雙月周' and @w5=0 and charindex(rtrim(@w1) ,where1)>0 )GO
drop table T