In my earlier article, I posted a solution how to calculate weekdays between weekends. Today I came across a situation where I need to calculate the weekends between two dates and the criterion is that weekend can be any day (not necessary Sunday only) of the week.
Given below is the function that calculates the weekends between two dates including the dates provided as input parameter. In some parts of the world weekends are on ‘Fridays’, ‘Saturdays’ and in other parts ‘Saturdays’ and ‘Sundays’.
In this function, you need to pass weekend as a parameter.
CREATE FUNCTION dbo.GetWeekendDays
( @StartDate DATETIME,
@EndDate DATETIME ,
@Weekend VARCHAR(50)
)
RETURNS INT
BEGIN
DECLARE @Xml AS XML
DECLARE @WEEKEND_DAYS AS INT
SET @Xml =CAST(('<a>'+replace(@Weekend,',' ,'</a><a>')+'</a>') AS XML)
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
SELECT @WEEKEND_DAYS=SUM(1) FROM n4
WHERE DATEADD(day,n-1,@startdate)<=@Enddate
AND DATENAME(dw,DateAdd(day,n-1,@startdate)) In (
SELECT A.value('.', 'varchar(max)') as [Column]
FROM @Xml.nodes('A') AS FN(A)
)
RETURN @WEEKEND_DAYS
END
GO
SELECT dbo.GetWeekendDays
('02/01/2013','02/28/2013','Saturday,Sunday')
as [Weekend Days]
GO
SELECT dbo.GetWeekendDays
('02/01/2013','02/28/2013','Friday,Saturday')
as [Weekend Days]
--OUTPUT
Weekend Days
------------
8
(1 row(s) affected)
Weekend Days
------------
8
(1 row(s) affected)
Have a better solution ?
SQL Server Portal 