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

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

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.

Written by: Elmozamil Elamir Hamid

7-6-2012.