--获取指定月的工作日天数(不考虑国家法定节假日,只排除周六周日, 已在休假行事历中设置休息天的也能获得工作天数)
CREATE FUNCTION [dbo].[getCountOfWorkdaysOfMonth](@year_in NVARCHAR(4), @month_in NVARCHAR(2), @restDayStr NVARCHAR(100)) --year_in 为四位年,month_in为两位月,@restDayStr为休息天字符串(用逗号分隔)
returns INT --使用函数的前要先写 set datefirst 1 这一句,让一周的第一天为1,即周一
AS
BEGIN
IF(@year_in IS NULL OR @month_in IS NULL)
BEGIN
RETURN NULL
END
DECLARE @firstDayOfMonth VARCHAR(10) --该月的第一天 yyyy-MM-dd格式
DECLARE @howManyDaysOfMonth INT --该月有多少天
DECLARE @dayCusor NUMERIC --循环用的迭代变量
DECLARE @countOfWorkdaysOfMonth INT --该月的工作日总数,初始化为0
DECLARE @dayOfADay VARCHAR --某天为周几 用来判断某天是否为周末
DECLARE @dateOfADay DATETIME --某天的日期 用来获取某天为周几,初始化为该月第一天的日期
SET @firstDayOfMonth = @year_in + '-' + @month_in + '-' + '01'
SET @dateOfADay = CONVERT(DATETIME,@firstDayOfMonth,121)
SET @howManyDaysOfMonth = CAST(DAY(DATEADD(MONTH, DATEDIFF(MONTH, 0, @dateOfADay) + 1, 0) - 1) AS INT)
IF (@restDayStr IS NOT NULL)
BEGIN
DECLARE @strLength INT
DECLARE @charindexOfComma INT
DECLARE @separator VARCHAR(1)
DECLARE @countOfRestDaysOfMonth INT
SET @separator = ','
SET @charindexOfComma = CHARINDEX(@separator,@restDayStr)
SET @strLength = LEN(@restDayStr)
SET @countOfRestDaysOfMonth = 1
SET @countOfWorkdaysOfMonth = @howManyDaysOfMonth - @countOfRestDaysOfMonth
WHILE @charindexOfComma > 0
BEGIN
SET @countOfRestDaysOfMonth = @countOfRestDaysOfMonth + 1
SET @restDayStr = SUBSTRING(@restDayStr, @charindexOfComma + 1, @strLength)
SET @charindexOfComma = CHARINDEX(@separator,@restDayStr)
END
SET @countOfWorkdaysOfMonth = @howManyDaysOfMonth - @countOfRestDaysOfMonth
END
ELSE
BEGIN
SET @countOfWorkdaysOfMonth = 0
SET @dayCusor = 1
WHILE @dayCusor <= @howManyDaysOfMonth
BEGIN
SET @dayOfADay = DATEPART(WEEKDAY,@dateOfADay)
IF @dayOfADay <> '6' AND @dayOfADay <> '7'
BEGIN
SET @countOfWorkdaysOfMonth = @countOfWorkdaysOfMonth + 1
END
SET @dateOfADay = @dateOfADay + 1
SET @dayCusor = @dayCusor + 1
END
END
RETURN @countOfWorkdaysOfMonth
END
[b]参考:[/b]
[url]http://wenku.baidu.com/link?url=fB3F0xZmwig9r2M_1pK4BGN6VcHPW6F3NZuABWU4ye6edhxEZQ0Tue5cOFJRzk1rMo6PPZ1iHdfACxCwRsPqAwzvheuvj7o_L994LQYArZS[/url]
[url]http://www.cnblogs.com/xionglee/articles/1444916.html[/url]