SQL中日期的比较问题

[yaosmail]

请问大家一个关于日期比较的问题:
公司里开票有两个时间类型周期
例如:
1,每月逢4开票,周期为5天,那么一个月的开票时间为4,5,6,7,8号,14,15,16,17,18号,24,25,26,27,28号
2,每星期逢1开票,周期为5天,那么每个月的星期一,二,三,四,五可以开票


以上逢几号或逢星期几开票和周期按不同的业务员都不同并且可以改变,也就是说都是参数控制,现在想用SQL语句实现,请教大家!谢谢.

[叶子]

--取出一年的所有星期一
declare @i int
set @i=1

select  substring(convert(varchar,dateadd(day,x,col),120),1,10),'星期一' from 
( 
select cast('2011-1-1' as datetime) as col 
)a cross join 
( 
select  top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x 
from(select 0 i union all select 1) b0 
cross join(select 0 i union all select 2) b1 
cross join(select 0 i union all select 4) b2 
cross join(select 0 i union all select 8) b3 
cross join(select 0 i union all select 16) b4 
cross join(select 0 i union all select 32) b5 
cross join(select 0 i union all select 64) b6 
cross join(select 0 i union all select 128) b7 
cross join(select 0 i union all select 256) b8 
order by 1 
)b 
where datepart(dw,dateadd(day,x,col))=2

--取出一年的所有星期四
declare @j int
set @j=5

select  substring(convert(varchar,dateadd(day,x,col),120),1,10),'星期四' from 
( 
select cast('2011-1-1' as datetime) as col 
)a cross join 
( 
select  top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x 
from(select 0 i union all select 1) b0 
cross join(select 0 i union all select 2) b1 
cross join(select 0 i union all select 4) b2 
cross join(select 0 i union all select 8) b3 
cross join(select 0 i union all select 16) b4 
cross join(select 0 i union all select 32) b5 
cross join(select 0 i union all select 64) b6 
cross join(select 0 i union all select 128) b7 
cross join(select 0 i union all select 256) b8 
order by 1 
)b 
where datepart(dw,dateadd(day,x,col))=5

[yaosmail]

数据库中存了逢几开票,周期,和类型(按月还是按星期)

[dawugui]

[Quote=引用楼主 yaosmail 的回复:]
请问大家一个关于日期比较的问题:
公司里开票有两个时间类型周期
例如:
1,每月逢4开票,周期为5天,那么一个月的开票时间为4,5,6,7,8号,14,15,16,17,18号,24,25,26,27,28号
2,每星期逢1开票,周期为5天,那么每个月的星期一,二,三,四,五可以开票


以上逢几号或逢星期几开票和周期按不同的业务员都不同并且可以改变,也就是说都是参数控制,现在想用……
[/Quote]
不管逢什么,把日期从当日加五天即可.
大致为:
select * from tb where datediff(dd,你输入的日期,数据库字段) between 0 and 5