Calculating Working Days in T-SQL

This code may be useful when working with working days. (excluding saturday and sunday and recalculate date)

Before getting into the code i want you to know that there are many ways to achieve this goal. One of the easiest way is to use a date table which containts a datetime string of each days. This table can be selected without choosing saturday and sunday.

However in my code, i used a table(Dim_Month_Day ) that contains numbers starting from 1 to 31 for cursor.

DECLARE @CURSOR_ID INT

DECLARE CRS_CURSOR CURSOR FOR

SELECT [day] FROM Dim_Month_Day ORDER BY [day] asc

OPEN CRS_CURSOR

FETCH NEXT FROM CRS_CURSOR INTO @CURSOR_ID

DECLARE @current_date DATETIME

DECLARE @real_date DATETIME

DECLARE @total_work_day INT

DECLARE @limit_day INT

SELECT @total_work_day=0

SELECT @limit_day= 12 -- this day corresponds to working days

SELECT @current_date=DATEADD(DD,-(DAY(GETDATE())-1),CONVERT(VARCHAR,GETDATE(),112))

WHILE @@FETCH_STATUS =0

BEGIN

IF datename(DW,@current_date)!='Saturday' AND datename(DW,@current_date)!='Sunday' AND @limit_day>@total_work_day

BEGIN

SET @total_work_day= @total_work_day+1

SET @real_date= @current_date

END

IF @limit_day>=@total_work_day

begin

SET @current_date= DATEADD(DD,1,@current_date)

end

FETCH NEXT FROM CRS_CURSOR INTO @CURSOR_ID

END

CLOSE CRS_CURSOR

DEALLOCATE CRS_CURSOR

At the end of the code, @real_date will give you what we wanted!