按常理推算,双休日都是可以直接计算的,但考虑到实际工作中,会出现因节假日
而调整双休日为工作日的情况,所以把双休日和节假日定义都纳入节假表中,处理时仅根据
节假日表确定指定日期是否为工作日
--*/
create table tb_Holiday(
HDate int primary key clustered,
Name nvarchar(50) not null)
go
--处理的自定义函数
create function f_workdateadd(
@dt smalldatetime, --要添加工作天数的日期
@workday int --要增加的工作天
)returns smalldatetime
as
begin
declare @dt_int int
set @dt_int=datediff(day,0,@dt)
if @workday>0
while @workday>0
select @dt_int=@dt_int+@workday
,@workday=count(*)
from tb_Holiday
where HDate between @dt_int and @dt_int+@workday
else
while @workday<0
select @dt_int=@dt_int+@workday,@workday=-count(*)
from tb_Holiday
where HDate between @dt_int+@workday and @dt_int
return(@dt)
end
go
--查询最近10个工作日内的记录,这样调用(注意在日期字段上建立索引,否则无法提高效率)
select * from tb where 日期 between dbo.f_workdateadd(getdate(),-10) and convert(char(10),getdate(),120)
--自动生成一年的所有周末的存储过程
create procedure GenerateWeekdays
@year int
as
begin
declare @dateFirstDefault int
declare @dayLoop datetime
declare @weekLoop int
declare @strYear char(4)
--检查年份是否越界
if not(@year between 1753 and 9999)
begin
return 0
end
--set @dateFirstDefault=@@datefirst --保存当前设置
--set datefirst 1 --将每周第一天设为周一
set @dayLoop=dateadd(year,@year-1900,'1900-01-01')
set @weekLoop=1
set @strYear=cast(@year as char(4))
--如果元旦是周日
if(datepart(weekday,@dayLoop)+@@datefirst=8)
begin
set @nameTemp=@strYear+'年 第'+cast(@weekLoop as char(2)) +'周 星期日'
insert into Holiday([Name],HDate,Remark)
values(@nameTemp,@dayLoop,@nameTemp)
set @dayLoop=DateAdd(day,1,@dayLoop)
set @weekLoop=@weekLoop+1
end
--取得元旦后第一个周六
set @dayLoop=DateAdd(day,6-datepart(weekday,@dayLoop),@dayLoop)
declare @intLoop int
set @intLoop=1
while(@intLoop<=52)
begin
--加入周六
set @nameTemp=@strYear+'年 第'+cast(@weekLoop as char(2)) +'周 星期六'
insert into Holiday([Name],HDate,Remark)
values(@nameTemp,@dayLoop,@nameTemp)
--加入周日
set @nameTemp=@strYear+'年 第'+cast(@weekLoop as char(2)) +'周 星期日'
insert into Holiday([Name],HDate,Remark)
values(@nameTemp,dateadd(day,1,@dayLoop),@nameTemp)
set @dayLoop=dateadd(day,7,@dayLoop)
set @weekLoop=@weekLoop+1
set @intLoop=@intLoop+1
end
--set datefirst @dateFirstDefault
return 1
end
这有一段我原来写的生成周末的代码
create table Holiday
(
HolidayID int identity not null,
"HDate" smalldatetime not null,
"Name" char(50) not null,
"Remark" char(100)
)
go
alter table "Holiday"
add constraint "PK_Holiday" primary key clustered ("HolidayID")
create unique index "Holiday_HDate_ind"
on Holiday("HDate")
create unique index "Holiday_Name_ind"
on Holiday("Name")
create function WorkDays(@mStartDate datetime,@mEndDate datetime)
returns integer
begin
declare @m integer
declare @n integer
if @mEndDate < @mStartDate set @n=-1 else
begin
set @m=datediff(dd,@mStartDate,@mEndDate)
set @n=(@m/7)*2
if @m % 7<>0
begin
if datepart(weekday,@mStartDate)>datepart(weekday,@mEndDate)
set @n=@n+2
if datepart(weekday,@mStartDate)=1 set @n=@n+1
if datepart(weekday,@mEndDate)=1 set @n=@n-1
end
set @n=@m-@n
end
return(@n)
end
go
create table tb_Holiday(
HDate smalldatetime primary key clustered,-- 日期
Type smallint,--增减
Name nvarchar(50) not null)
go
insert into tb_Holiday
select '2005-1-1',0,'元旦' union -- 周六
select '2005-1-2',0,'元旦' union -- 周日
select '2005-1-3',-1,'元旦-' union
select '2005-2-5',1,'春节+' union
select '2005-2-6',1,'春节+' union
select '2005-2-9',-1,'春节-' union
select '2005-2-10',-1,'春节-' union
select '2005-2-11',-1,'春节-' union
select '2005-2-12',0,'春节' union -- 周六
select '2005-2-13',0,'春节' union -- 周日
select '2005-2-14',-1,'春节-' union
select '2005-2-15',-1,'春节-'
select dbo.WorkDays('2004-12-20',getdate())+sum(Type) from tb_Holiday where HDate >'2004-12-20'
create function WorkDays(@mStartDate datetime,@mEndDate datetime)
returns integer
begin
declare @m integer
declare @n integer
if @mEndDate < @mStartDate set @n=-1 else
begin
set @m=datediff(dd,@mStartDate,@mEndDate)
set @n=(@m/7)*2
if @m % 7<>0
begin
if datepart(weekday,@mStartDate)>datepart(weekday,@mEndDate)
set @n=@n+2
if datepart(weekday,@mStartDate)=1 set @n=@n+1
if datepart(weekday,@mEndDate)=1 set @n=@n-1
end
set @n=@m-@n
end
return(@n)
end
delphi 代码是
function WorksDay( //计算两个日期间有多少个工作日
mStartDate: TDate; //开始日期
mEndDate: TDate //终止日期
): Integer; //返回两个日期间的工作日,如果输入错误则返回-1
var m,n:integer;
begin
Result := -1;
if mEndDate < mStartDate then Exit;
m:=DaysBetween(mStartDate,mEndDate);
n:=(m div 7)*2;
if m mod 7<>0 then
begin
if DayOfWeek(mStartDate)>DayOfWeek(mEndDate) then n:=n+2;
if DayOfWeek(mStartDate)=1 then n:=n+1;
if DayOfWeek(mEndDate)=1 then n:=n-1;
end;
result:=m-n;
end;