select sum(case datepart(weekday,日期)when 6 then 0 when 7 then 0 else 1 end) from where datepart(year,日期) = 2009 and datepart(month,日期) =
--zhuan工作日处理函数(标准节假日) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_WorkDay] GO--计算两个日期相差的工作天数 CREATE FUNCTION f_WorkDay( @dt_begin datetime, --计算的开始日期 @dt_end datetime --计算的结束日期 )RETURNS int AS BEGIN DECLARE @workday int,@i int,@bz bit,@dt datetime IF @dt_begin>@dt_end SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt ELSE SET @bz=0 SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1, @workday=@i/7*5, @dt_begin=DATEADD(Day,@i/7*7,@dt_begin) WHILE @dt_begin<=@dt_end BEGIN SELECT @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5 THEN @workday+1 ELSE @workday END, @dt_begin=@dt_begin+1 END RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END) END GO/*=================================================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_WorkDayADD] GO--在指定日期上,增加指定工作天数后的日期 CREATE FUNCTION f_WorkDayADD( @date datetime, --基础日期 @workday int --要增加的工作日数 )RETURNS datetime AS BEGIN DECLARE @bz int --增加整周的天数 SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END ,@date=DATEADD(Week,@workday/5,@date) ,@workday=@workday%5 --增加不是整周的工作天数 WHILE @workday<>0 SELECT @date=DATEADD(Day,@bz,@date), @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5 THEN @workday-@bz ELSE @workday END --避免处理后的日期停留在非工作日上 WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6) SET @date=DATEADD(Day,@bz,@date) RETURN(@date) END 有点不一样,不过楼主参考
declare @month varchar(10) select @month = '2009-10'select count(1) as 天数 from master..spt_values where type = 'P' and convert(varchar(7),dateadd(day,number,@month+'-01'),120) = @month and datepart(weekday,dateadd(day,number,@month+'-01')) not in (1,7)------------------------ 22
select sum (case datepart(weekday,日期)IN(6,7) then 0 else 1 end) from TB where datepart(year,日期) = 2009 and datepart(month,日期) = ....
create function fn_test(@begin datetime,@end datetime) returns int As BEGIN declare @i int,@j int set @i=0 set @j=0 if @end> @begin begin while dateadd(d,@i,@begin) <=@end begin if datepart(weekday,dateadd(d,@i,@begin)) not in(1,7) set @j=@j+1 set @i=@i+1 end end
return @j END
转贴 ---邹建 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_workday]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_workday] GO
--如果是得到指定月份,稍改一下就行了 create function f_workday( @年月 varchar(7) --要查询的年月,格式要求:2004-7 )returns int as begin declare @dt_begin datetime,@dt_end datetime select @dt_begin=@年月+'-01' ,@dt_end=dateadd(month,1,@dt_begin)-1 declare @dt datetime,@re int,@i int if @dt_begin>@dt_end select @dt=@dt_begin ,@dt_begin=@dt_end ,@dt_end=@dt select @i=datediff(day,@dt_begin,@dt_end)+1 ,@re=@i/7*2 ,@dt=dateadd(day,@i/7*7-1,@dt_begin) while @dt<@dt_end select @re=case when datepart(weekday,@dt) in(1,7) then @re+1 else @re end ,@dt=@dt+1 return(@i-@re) end go
--调用 select dbo.f_workday('2004-07')
DECLARE @DATE DATETIME SELECT @DATE='2009-12-1' SELECT COUNT(1) FROM MASTER..SPT_VALUES WHERE TYPE='P' AND DATEADD(DAY,NUMBER,@DATE)<=DATEADD(DAY,-1,DATEADD(MONTH,1,@DATE)) AND DATEPART(DW,DATEADD(DAY,NUMBER,@DATE)) NOT IN (7,1)--23
declare @sdate datetime declare @edate datetime set @sdate = '2009-12-01' set @edate = cast(convert(varchar(7),dateadd(mm,1,@sdate),120) + '-01' as datetime) - 1select dateadd(dd,num,@sdate) dt 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 and datepart(weekday , dateadd(dd,num,@sdate)) not in (7,1) order by dt/* dt ------------------------------------------------------ 2009-12-01 00:00:00.000 2009-12-02 00:00:00.000 2009-12-03 00:00:00.000 2009-12-04 00:00:00.000 2009-12-07 00:00:00.000 2009-12-08 00:00:00.000 2009-12-09 00:00:00.000 2009-12-10 00:00:00.000 2009-12-11 00:00:00.000 2009-12-14 00:00:00.000 2009-12-15 00:00:00.000 2009-12-16 00:00:00.000 2009-12-17 00:00:00.000 2009-12-18 00:00:00.000 2009-12-21 00:00:00.000 2009-12-22 00:00:00.000 2009-12-23 00:00:00.000 2009-12-24 00:00:00.000 2009-12-25 00:00:00.000 2009-12-28 00:00:00.000 2009-12-29 00:00:00.000 2009-12-30 00:00:00.000 2009-12-31 00:00:00.000(所影响的行数为 23 行) */
declare @sdate datetime declare @edate datetime set @sdate = '2009-11-01' set @edate = cast(convert(varchar(7),dateadd(mm,1,@sdate),120) + '-01' as datetime) - 1select dateadd(dd,num,@sdate) dt 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 and datepart(weekday , dateadd(dd,num,@sdate)) not in (7,1) order by dt/* dt ------------------------------------------------------ 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-09 00:00:00.000 2009-11-10 00:00:00.000 2009-11-11 00:00:00.000 2009-11-12 00:00:00.000 2009-11-13 00:00:00.000 2009-11-16 00:00:00.000 2009-11-17 00:00:00.000 2009-11-18 00:00:00.000 2009-11-19 00:00:00.000 2009-11-20 00:00:00.000 2009-11-23 00:00:00.000 2009-11-24 00:00:00.000 2009-11-25 00:00:00.000 2009-11-26 00:00:00.000 2009-11-27 00:00:00.000 2009-11-30 00:00:00.000(所影响的行数为 21 行) */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO--计算两个日期相差的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime, --计算的开始日期
@dt_end datetime --计算的结束日期
)RETURNS int
AS
BEGIN
DECLARE @workday int,@i int,@bz bit,@dt datetime
IF @dt_begin>@dt_end
SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
ELSE
SET @bz=0
SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
@workday=@i/7*5,
@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
WHILE @dt_begin<=@dt_end
BEGIN
SELECT @workday=CASE
WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
THEN @workday+1 ELSE @workday END,
@dt_begin=@dt_begin+1
END
RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO/*=================================================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO--在指定日期上,增加指定工作天数后的日期
CREATE FUNCTION f_WorkDayADD(
@date datetime, --基础日期
@workday int --要增加的工作日数
)RETURNS datetime
AS
BEGIN
DECLARE @bz int
--增加整周的天数
SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END
,@date=DATEADD(Week,@workday/5,@date)
,@workday=@workday%5
--增加不是整周的工作天数
WHILE @workday<>0
SELECT @date=DATEADD(Day,@bz,@date),
@workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5
THEN @workday-@bz ELSE @workday END
--避免处理后的日期停留在非工作日上
WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6)
SET @date=DATEADD(Day,@bz,@date)
RETURN(@date)
END
有点不一样,不过楼主参考
select @month = '2009-10'select count(1) as 天数
from master..spt_values
where type = 'P'
and convert(varchar(7),dateadd(day,number,@month+'-01'),120) = @month
and datepart(weekday,dateadd(day,number,@month+'-01')) not in (1,7)------------------------
22
(case datepart(weekday,日期)IN(6,7) then 0 else 1 end)
from TB
where datepart(year,日期) = 2009 and datepart(month,日期) =
....
returns int
As
BEGIN
declare @i int,@j int
set @i=0
set @j=0
if @end> @begin
begin
while dateadd(d,@i,@begin) <=@end
begin
if datepart(weekday,dateadd(d,@i,@begin)) not in(1,7)
set @j=@j+1
set @i=@i+1
end
end
return @j END
---邹建
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_workday]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_workday]
GO
--如果是得到指定月份,稍改一下就行了
create function f_workday(
@年月 varchar(7) --要查询的年月,格式要求:2004-7
)returns int
as
begin
declare @dt_begin datetime,@dt_end datetime
select @dt_begin=@年月+'-01'
,@dt_end=dateadd(month,1,@dt_begin)-1
declare @dt datetime,@re int,@i int
if @dt_begin>@dt_end
select @dt=@dt_begin
,@dt_begin=@dt_end
,@dt_end=@dt
select @i=datediff(day,@dt_begin,@dt_end)+1
,@re=@i/7*2
,@dt=dateadd(day,@i/7*7-1,@dt_begin)
while @dt<@dt_end
select @re=case when datepart(weekday,@dt) in(1,7)
then @re+1 else @re end
,@dt=@dt+1
return(@i-@re)
end
go
--调用
select dbo.f_workday('2004-07')
SELECT @DATE='2009-12-1'
SELECT COUNT(1)
FROM MASTER..SPT_VALUES
WHERE TYPE='P' AND DATEADD(DAY,NUMBER,@DATE)<=DATEADD(DAY,-1,DATEADD(MONTH,1,@DATE))
AND DATEPART(DW,DATEADD(DAY,NUMBER,@DATE)) NOT IN (7,1)--23
declare @edate datetime
set @sdate = '2009-12-01'
set @edate = cast(convert(varchar(7),dateadd(mm,1,@sdate),120) + '-01' as datetime) - 1select
dateadd(dd,num,@sdate) dt
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 and datepart(weekday , dateadd(dd,num,@sdate)) not in (7,1)
order by dt/*
dt
------------------------------------------------------
2009-12-01 00:00:00.000
2009-12-02 00:00:00.000
2009-12-03 00:00:00.000
2009-12-04 00:00:00.000
2009-12-07 00:00:00.000
2009-12-08 00:00:00.000
2009-12-09 00:00:00.000
2009-12-10 00:00:00.000
2009-12-11 00:00:00.000
2009-12-14 00:00:00.000
2009-12-15 00:00:00.000
2009-12-16 00:00:00.000
2009-12-17 00:00:00.000
2009-12-18 00:00:00.000
2009-12-21 00:00:00.000
2009-12-22 00:00:00.000
2009-12-23 00:00:00.000
2009-12-24 00:00:00.000
2009-12-25 00:00:00.000
2009-12-28 00:00:00.000
2009-12-29 00:00:00.000
2009-12-30 00:00:00.000
2009-12-31 00:00:00.000(所影响的行数为 23 行)
*/
declare @edate datetime
set @sdate = '2009-11-01'
set @edate = cast(convert(varchar(7),dateadd(mm,1,@sdate),120) + '-01' as datetime) - 1select
dateadd(dd,num,@sdate) dt
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 and datepart(weekday , dateadd(dd,num,@sdate)) not in (7,1)
order by dt/*
dt
------------------------------------------------------
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-09 00:00:00.000
2009-11-10 00:00:00.000
2009-11-11 00:00:00.000
2009-11-12 00:00:00.000
2009-11-13 00:00:00.000
2009-11-16 00:00:00.000
2009-11-17 00:00:00.000
2009-11-18 00:00:00.000
2009-11-19 00:00:00.000
2009-11-20 00:00:00.000
2009-11-23 00:00:00.000
2009-11-24 00:00:00.000
2009-11-25 00:00:00.000
2009-11-26 00:00:00.000
2009-11-27 00:00:00.000
2009-11-30 00:00:00.000(所影响的行数为 21 行)
*/