# 求助写一个根据年月自动计算出当月有多少天工作日的自定义函数，只需要考虑周末，不用考虑法定节假日

z418841875 2015-09-29 09:58:16

...全文
326 5 点赞 打赏 收藏 举报

5 条回复

Tiger_Zhao 2015-09-30
``````-- 参数
DECLARE @ym varchar(6)
SET @ym = '201509'
-- 实现
SET DATEFIRST 1

DECLARE @firstDay datetime
SET @firstDay = CONVERT(datetime,@ym+'01',112)

SELECT COUNT(*) 工作日数
FROM (
FROM master..spt_values
WHERE type = 'p'
AND number < 31
) t
WHERE month(dt) = month(@firstDay)
AND DATEPART(weekday,dt) < 6``````

``````   工作日数
-----------
22``````
• 打赏
• 举报

``````

declare @month varchar(10) = '201509'
declare @startdate varchar(10)
declare @enddate varchar(10)

set @startdate = STUFF(@month,5,0,'-') + '-01'
select COUNT(*) as '非周末天数'
from master..spt_values
where type ='p' and number < DATEDIFF(day,@startdate,@enddate)
and  DATEPART(DW, DATEADD(day, number ,@startdate)) not in (7,1)

-----------
22

(1 行受影响)

``````
• 打赏
• 举报

zbdzjx 2015-09-30
``````declare @ym varchar(6)
select @ym='201509'

select COUNT(*) from (
select DATEADD(day, number, convert(datetime, @ym+'01')) allday
from master..spt_values
where type='p' and number<31
) aa
where DATEPART(weekday, allday) not in (1,7) and DATEPART(MONTH, allday)=CONVERT(int, substring(@ym,5,2))``````
• 打赏
• 举报

reenjie 2015-09-30

``````
create function [dbo].[func_GetNumOfWorkDays](
@yearMonth varchar(6)
)
returns int
begin
declare @year int=left(@yearMonth,4),         --截取年份
@month int=right(@yearMonth,2),       --截取月份
@startDay date='',                    --该月开始日期
@endDay date='',                      --该月结束日期
@start int,                           --该月开始日期对应的一周中的第几天(注意星期天对应的是1，而周六对应的是7)
@end int                              --该月结束日期对应的一周中的第几天(注意星期天对应的是1，而周六对应的是7)
set @startDay=convert(varchar(4),@year)+'-'+convert(varchar(4),@month)+'-01'
if(@month=12)
begin
end
else
begin
end
set @start=datepart(weekday,@startDay)
set @end=datepart(weekday,@endDay)
if(@start in(1,7))
begin
set @start=2
end
if(@end in(1,7))
begin
set @end=6
end
return (@end-@start+1)%5+20
end
``````
• 打赏
• 举报

z418841875 2015-09-30

• 打赏
• 举报

2.1w+

MS-SQL Server 疑难问题

2015-09-29 09:58