27,579
社区成员
发帖
与我相关
我的任务
分享
--该过程可以得出开始日期和结束日期的所有日期
--只要在后边加条件剔除你自己定义的休息日就可以了
create table Mon
(
ID int identity(1,1),
Mon varchar(6)
)
GO
DECLARE
@BeginMonth varchar(6),
@EndMonth varchar(6)
SELECT
@BeginMonth='20091001', --开始日期
@EndMonth='20091030' --结束日期
SELECT
CONVERT(VARCHAR(8),DATEADD(day,number,@BeginMonth+'01'),112)
FROM
master..spt_values
WHERE
type='P'
and
DATEADD(day,number,@BeginMonth+'01')<=@EndMonth+'01'
[/Quote]
declare @s table (a datetime)
insert @s select '2009-10-10' union all select
'2009-10-14' union all select
'2009-10-18' union all select
'2009-10-19' union all select
'2009-10-25'
declare @date datetime,@n int
set @date='2009-10-08'
set @n=30
select convert(varchar(10),ko.kop,120)
from(
select DATEADD(day,number,@date) as kop
from master..spt_values
where type='p' and number between 0 and @n-1) ko left join @s k
on DATEDIFF(DAY,ko.kop ,k.a)=0
where k.a is null
/*
(5 行受影响)
----------
2009-10-08
2009-10-09
2009-10-11
2009-10-12
2009-10-13
2009-10-15
2009-10-16
2009-10-17
2009-10-20
2009-10-21
2009-10-22
2009-10-23
2009-10-24
2009-10-26
2009-10-27
2009-10-28
2009-10-29
2009-10-30
2009-10-31
2009-11-01
2009-11-02
2009-11-03
2009-11-04
2009-11-05
2009-11-06
*/
declare @sdate datetime
declare @edate datetime
set @sdate = '2009-10-08 00:00:000'
set @edate = dateadd(day,30, @sdate)
select
dateadd(dd,num,@sdate) [day]
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate
order by [day]
/*
day
------------------------------------------------------
2009-10-08 00:00:00.000
2009-10-09 00:00:00.000
2009-10-10 00:00:00.000
2009-10-11 00:00:00.000
2009-10-12 00:00:00.000
2009-10-13 00:00:00.000
2009-10-14 00:00:00.000
2009-10-15 00:00:00.000
2009-10-16 00:00:00.000
2009-10-17 00:00:00.000
2009-10-18 00:00:00.000
2009-10-19 00:00:00.000
2009-10-20 00:00:00.000
2009-10-21 00:00:00.000
2009-10-22 00:00:00.000
2009-10-23 00:00:00.000
2009-10-24 00:00:00.000
2009-10-25 00:00:00.000
2009-10-26 00:00:00.000
2009-10-27 00:00:00.000
2009-10-28 00:00:00.000
2009-10-29 00:00:00.000
2009-10-30 00:00:00.000
2009-10-31 00:00:00.000
2009-11-01 00:00:00.000
2009-11-02 00:00:00.000
2009-11-03 00:00:00.000
2009-11-04 00:00:00.000
2009-11-05 00:00:00.000
2009-11-06 00:00:00.000
2009-11-07 00:00:00.000
(所影响的行数为 31 行)
*/