请问大家一个关于日期比较的问题:
公司里开票有两个时间类型周期
例如:
1,每月逢4开票,周期为5天,那么一个月的开票时间为4,5,6,7,8号,14,15,16,17,18号,24,25,26,27,28号
2,每星期逢1开票,周期为5天,那么每个月的星期一,二,三,四,五可以开票
以上逢几号或逢星期几开票和周期按不同的业务员都不同并且可以改变,也就是说都是参数控制,现在想用SQL语句实现,请教大家!谢谢.
公司里开票有两个时间类型周期
例如:
1,每月逢4开票,周期为5天,那么一个月的开票时间为4,5,6,7,8号,14,15,16,17,18号,24,25,26,27,28号
2,每星期逢1开票,周期为5天,那么每个月的星期一,二,三,四,五可以开票
以上逢几号或逢星期几开票和周期按不同的业务员都不同并且可以改变,也就是说都是参数控制,现在想用SQL语句实现,请教大家!谢谢.
大致为:
select * from tb where datediff(dd,你输入的日期,数据库字段) between 0 and 5
--取出一年的所有星期一
declare @i int
set @i=1select 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=5select 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