T-SQL: Eliminate holidays (weekends, specific day)

One of the tasks that may we have to deal with is how to eliminate weekends and holidays from a period using SQL server, e.g. if you want to calculate number of business days for specific company you may need to eliminate Saturday and Sunday or Friday.

To do this you can use DATENAME function to get name of the day and then eliminate this day from your selection.

In the bellow query I calculate the number of business days excluding Friday.

To do this I used Function and then call this function in may select statement.

ALTER FUNCTION  [dbo].[Caldates]  
( 
@SDate Date, 
@EDate Date
) 
RETURNS int
AS
BEGIN
DECLARE @WeekEnd AS NVARCHAR(15) 
DECLARE @Days AS INT
SET @WeekEnd = 'Friday' – you can create more than one variable for different days 
SET @Days = 0 
WHILE @SDate <= @EDate 
BEGIN
IF DATENAME(dw, @SDate) <> @WeekEnd 
BEGIN
SET @Days = @Days + 1 –- you can use this to calculate number of hours spend in business days by multiplying it by the number of hours per day. 
END
SET @SDate = DATEADD(day,1,@SDate) 
END
return @Days 
END
Your select  statement 
SELECT [dbo].[ Caldates] (‘2012-01-01’, ‘2012-06-07’)  
GO 

You can change the above query as you want, and please share another ways to play with date.