22,209
社区成员
发帖
与我相关
我的任务
分享
create table 周末及法定节假日表(节日与周末名称 varchar(200),日期 datetime)
insert 周末及法定节假日表 values('元旦','2009-01-01')
insert 周末及法定节假日表 values('春节','2009-01-25')
insert 周末及法定节假日表 values('春节','2009-01-26')
insert 周末及法定节假日表 values('春节','2009-01-27')
insert 周末及法定节假日表 values('清明节','2009-04-04')
insert 周末及法定节假日表 values('劳动节','2009-05-01')
insert 周末及法定节假日表 values('端午节','2009-05-28')
--insert 周末及法定节假日表 values('中秋节','2009-10-04')
--insert 周末及法定节假日表 values('国庆节','2009-10-01')
--insert 周末及法定节假日表 values('国庆节','2009-10-02')
--insert 周末及法定节假日表 values('国庆节','2009-10-03')
declare @begdate datetime,@enddate datetime
select @begdate = '2009-01-01',@enddate = '2009-06-01'
while @begdate <=@enddate
begin
insert 周末及法定节假日表
select datename(w,@begdate),@begdate where datename(w,@begdate) in('星期六','星期日')
set @begdate=@begdate+1
end
--select * from 周末及法定节假日表
select datepart(dayofyear,'2009-06-01')-count(distinct 日期) as 刨除周六日还有中间的节假日的天数 from 周末及法定节假日表
drop table 周末及法定节假日表
/*
刨除周六日还有中间的节假日的天数
----------------
103
(所影响的行数为 1 行)
*/
create table 周末及法定节假日表(节日与周末名称 varchar(200),日期 datetime)
insert 周末及法定节假日表 values('元旦','2009-01-01')
insert 周末及法定节假日表 values('春节','2009-01-25')
insert 周末及法定节假日表 values('春节','2009-01-26')
insert 周末及法定节假日表 values('春节','2009-01-27')
insert 周末及法定节假日表 values('清明节','2009-04-04')
insert 周末及法定节假日表 values('劳动节','2009-05-01')
insert 周末及法定节假日表 values('端午节','2009-05-28')
insert 周末及法定节假日表 values('中秋节','2009-01-01')
insert 周末及法定节假日表 values('国庆节','2009-10-01')
insert 周末及法定节假日表 values('国庆节','2009-10-02')
insert 周末及法定节假日表 values('中秋节','2009-10-04')
declare @begdate datetime,@enddate datetime
select @begdate = '2009-01-01',@enddate = '2009-12-31'
while @begdate <=@enddate
begin
insert 周末及法定节假日表
select datename(w,@begdate),@begdate where datename(w,@begdate) in('星期六','星期日')
set @begdate=@begdate+1
end
--select * from 周末及法定节假日表
select datepart(dayofyear,'2009-12-31')-count(distinct 日期) as 刨除周六日还有中间的节假日的天数 from 周末及法定节假日表
drop table 周末及法定节假日表
/*
刨除周六日还有中间的节假日的天数
----------------
254
(所影响的行数为 1 行)
*/
--1.临时表
select top 8000 identity(int,0,1) as id into # from syscolumns a,syscolumns b
--2.节假日表
create table tb(dt datetime)
insert into tb values('2009-01-01')
insert into tb values('2009-01-25')
insert into tb values('2009-01-26')
insert into tb values('2009-01-27')
insert into tb values('2009-01-28')
insert into tb values('2009-01-29')
insert into tb values('2009-01-30')
insert into tb values('2009-01-31')
insert into tb values('2009-05-01')
insert into tb values('2009-05-02')
insert into tb values('2009-05-03')
go
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2009-01-01'
set @dt2 = '2009-06-01'
select count(dateadd(day , t.id , @dt1)) 数量 from # t where dateadd(day , t.id , @dt1) <= @dt2 and dateadd(day , t.id , @dt1) not in (select dt from tb) and datepart(weekday , dateadd(day , t.id , @dt1)) not in (1,7)
/*
数量
-----------
101
(所影响的行数为 1 行)
*/
select dateadd(day , t.id , @dt1) dt from # t where dateadd(day , t.id , @dt1) <= @dt2 and dateadd(day , t.id , @dt1) not in (select dt from tb) and datepart(weekday , dateadd(day , t.id , @dt1)) not in (1,7)
/*
dt
------------------------------------------------------
2009-01-02 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-12 00:00:00.000
2009-01-13 00:00:00.000
2009-01-14 00:00:00.000
2009-01-15 00:00:00.000
2009-01-16 00:00:00.000
2009-01-19 00:00:00.000
2009-01-20 00:00:00.000
2009-01-21 00:00:00.000
2009-01-22 00:00:00.000
2009-01-23 00:00:00.000
2009-02-02 00:00:00.000
2009-02-03 00:00:00.000
2009-02-04 00:00:00.000
2009-02-05 00:00:00.000
2009-02-06 00:00:00.000
2009-02-09 00:00:00.000
2009-02-10 00:00:00.000
2009-02-11 00:00:00.000
2009-02-12 00:00:00.000
2009-02-13 00:00:00.000
2009-02-16 00:00:00.000
2009-02-17 00:00:00.000
2009-02-18 00:00:00.000
2009-02-19 00:00:00.000
2009-02-20 00:00:00.000
2009-02-23 00:00:00.000
2009-02-24 00:00:00.000
2009-02-25 00:00:00.000
2009-02-26 00:00:00.000
2009-02-27 00:00:00.000
2009-03-02 00:00:00.000
2009-03-03 00:00:00.000
2009-03-04 00:00:00.000
2009-03-05 00:00:00.000
2009-03-06 00:00:00.000
2009-03-09 00:00:00.000
2009-03-10 00:00:00.000
2009-03-11 00:00:00.000
2009-03-12 00:00:00.000
2009-03-13 00:00:00.000
2009-03-16 00:00:00.000
2009-03-17 00:00:00.000
2009-03-18 00:00:00.000
2009-03-19 00:00:00.000
2009-03-20 00:00:00.000
2009-03-23 00:00:00.000
2009-03-24 00:00:00.000
2009-03-25 00:00:00.000
2009-03-26 00:00:00.000
2009-03-27 00:00:00.000
2009-03-30 00:00:00.000
2009-03-31 00:00:00.000
2009-04-01 00:00:00.000
2009-04-02 00:00:00.000
2009-04-03 00:00:00.000
2009-04-06 00:00:00.000
2009-04-07 00:00:00.000
2009-04-08 00:00:00.000
2009-04-09 00:00:00.000
2009-04-10 00:00:00.000
2009-04-13 00:00:00.000
2009-04-14 00:00:00.000
2009-04-15 00:00:00.000
2009-04-16 00:00:00.000
2009-04-17 00:00:00.000
2009-04-20 00:00:00.000
2009-04-21 00:00:00.000
2009-04-22 00:00:00.000
2009-04-23 00:00:00.000
2009-04-24 00:00:00.000
2009-04-27 00:00:00.000
2009-04-28 00:00:00.000
2009-04-29 00:00:00.000
2009-04-30 00:00:00.000
2009-05-04 00:00:00.000
2009-05-05 00:00:00.000
2009-05-06 00:00:00.000
2009-05-07 00:00:00.000
2009-05-08 00:00:00.000
2009-05-11 00:00:00.000
2009-05-12 00:00:00.000
2009-05-13 00:00:00.000
2009-05-14 00:00:00.000
2009-05-15 00:00:00.000
2009-05-18 00:00:00.000
2009-05-19 00:00:00.000
2009-05-20 00:00:00.000
2009-05-21 00:00:00.000
2009-05-22 00:00:00.000
2009-05-25 00:00:00.000
2009-05-26 00:00:00.000
2009-05-27 00:00:00.000
2009-05-28 00:00:00.000
2009-05-29 00:00:00.000
2009-06-01 00:00:00.000
(所影响的行数为 101 行)
*/
drop table # , tb