求SQL写一个月里有几个工作日

if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [tb_Holiday]

GO



--定义节假日表

CREATE TABLE tb_Holiday(

HDate smalldatetime primary key clustered, --节假日期

Name nvarchar(50) not null)             --假日名称

GO



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

 IF @dt_begin>@dt_end

  RETURN(DATEDIFF(Day,@dt_begin,@dt_end)

   +1-(

    SELECT COUNT(*) FROM tb_Holiday

    WHERE HDate BETWEEN @dt_begin AND @dt_end))

 RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)

  +1-(

   SELECT COUNT(*) FROM tb_Holiday

   WHERE HDate BETWEEN @dt_end AND @dt_begin)))

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

 IF @workday>0

  WHILE @workday>0

   SELECT @date=@date+@workday,@workday=count(*)

   FROM tb_Holiday

   WHERE HDate BETWEEN @date AND @date+@workday

 ELSE

  WHILE @workday<0

   SELECT @date=@date+@workday,@workday=-count(*)

   FROM tb_Holiday

   WHERE HDate BETWEEN @date AND @date+@workday

 RETURN(@date)

END

--计算个日期之间的工作日(周末双休,不考虑特殊节日)



create function workday 



(



@beg_time datetime,



@end_time datetime



)



returns int



as



begin 



declare @n int



set @n=(



select 工作日=days/7*5+days%7



             -case when 6 between wd and wd+days%7-1 then 1 end



             -case when 7 between wd and wd+days%7-1 then 1 end



from(select datediff(day,@beg_time,@end_time)+1 as days,



     datepart(weekday,@beg_time+@@datefirst-1) as wd



) k



)



return @N



end



go



declare @beg_time datetime,@end_time datetime



set @beg_time='2010-01-01' 



set @end_time='2010-06-28'



--执行函数



select 工作日=dbo.workday(@beg_time,@end_time)



/*



工作日



-----------



127





*/

DECLARE @YEAR VARCHAR(4),@MONTH VARCHAR(2),@TIME DATETIME,@OTIME DATETIME,@COUNT INT

--@YEAR  年份

--@MONTH 月份

--@COUNT 某年某月的工作日数

SET @YEAR='2009'--DATEPART(YEAR,GETDATE())--设置你要计算某月工作日的年份

SET @MONTH='02'--DATEPART(MONTH,GETDATE())--设置你要计算某月工作日的月份

SET @COUNT=0

SET @TIME=@YEAR+'-'+@MONTH+'-01'

SET @OTIME=DATEADD(MONTH,1,@TIME)

WHILE @TIME<@OTIME

  BEGIN

    IF DATEPART(WEEKDAY,@TIME)<>1 AND DATEPART(WEEKDAY,@TIME)<>7

      BEGIN

        SET @COUNT=@COUNT+1

      END

    SET @TIME=DATEADD(DAY,1,@TIME)

  END

SELECT @COUNT

declare @dts datetime,@dte datetime

select @dts='2010-04-01',@dte='2010-04-30'

select count(*) 

from master..spt_values 

where dateadd(day,number,@dts)<=@dte and type='P' and

(datepart(weekday,dateadd(day,number,@dts))+@@datefirst-1)%7 between 1 and 5

/*

-----------

22



*/

declare @date datetime   

set @date=GETDATE()

  

select [workday]='这个月有'+ltrim(COUNT(1))+'个工作日'

from master..spt_values   

where type='p'  

  and number< datediff(dd,@date,dateadd(mm,1,@date)) 

  and DATEPART(w,DATEADD(mm,DATEDIFF(mm,0,@date),0)+number+@@DATEFIRST-1) not in(6,7)

/*

workday

----------------------------

这个月有22个工作日



(1 行受影响)

*/