June 7, 2005 at 9:23 am
I need assistance in developing a UDF which will return a boolean value for an input value. The input value would be GETDATE() and I need to return true if GETDATE() is the 3rd Business Day of the current month. Saturdays are considered business days as are Mon-Fri.
June 7, 2005 at 9:53 am
Here's an exemple of a count of business days between 2 dates (excluding saturdays). It also takes into consideration the holidays. Msg me if you need help modifying this to suit your needs:
CREATE FUNCTION dbo.fnBusinessDays (@start datetime, @end datetime)
RETURNS INT
AS
BEGIN
--Function designed to calculate the number of business days between two dates.
DECLARE
@wks int
,@days int
,@sdays int
,@edays int
,@Holidays int
-- Find the number of weeks between the dates. Subtract 1
-- since we do not want to count the current week.
SET @wks = datediff(week, @start, @end) - 1
-- calculate the number of days in these full wks.
SET @days = @wks * 5
-- Get the number of days in the week of the start date. This is the days
-- between Saturday (datepart=7) and the startdate. We also remove the
-- Sunday (datepart=1). If the first day is a Saturday, do not exclude
-- this twice.
IF datepart( dw, @start) = 7
SET @sdays = 7 - datepart( dw, @start)
ELSE
SET @sdays = 7 - datepart( dw, @start) - 1
-- Calculate the days in the last week. These are not included in the
-- week calculation. Since we are starting with the end date, we only
-- remove the Sunday (datepart=1) from the number of days. If the end
-- date is Saturday, correct for this.
IF datepart( dw, @end) = 7
SET @edays = datepart( dw, @end) - 2
ELSE
SET @edays = datepart( dw, @end) - 1
SET @Holidays = (SELECT COUNT(*) FROM dbo.DatesFeries WHERE DateFerie BETWEEN @start AND @end AND CCQ_ONLY = 0)
-- Sum everything together.
SET @days = @days + @sdays + @edays - @Holidays
RETURN @days
END
June 8, 2005 at 9:20 am
You're right... come to think of it I don't even use this function. I Always refer to my holidays table (don't have a full calendar because I have no need for one at the moment).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply