Adaugare zile lucratoare

Situatie

Pentru cazurile in care ne este necesar am facut o functie ce aduga un numar de zilela o data, dar zilele luate in calcul sunt doar zile lucratoare.

Functia primeste 2 parametri.

Unul este data de start iar celalat numarul de zile dorit a fi adaugat la acea data.

Solutie

— SELECT dbo.[ BD_AddWorkingDays] (‘20200306′, 1)

ALTER FUNCTION [dbo].BD_AddWorkingDays
(
@StartDate datetime,
@N INT
)
RETURNS datetime
AS
BEGIN

declare @SaturdayDW int
declare @SundayDW int
set @SaturdayDW = DATEPART(DW,CONVERT(datetime,’2019 January 5′))
set @SundayDW = DATEPART(DW,CONVERT(datetime,’2019 January 6’))

if @N=0
begin
set @N=1
set @StartDate=DATEADD(DAY,-1,@StartDate)
end

declare @increment int
if @N>=0 set @increment = 1 else set @increment = -1

declare @CountDays int
set @CountDays=0
declare @LoopDate datetime
set @LoopDate = @StartDate

while @CountDays<ABS(@N)
begin
set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
while
DATEPART(DW,@LoopDate)= @SaturdayDW
or DATEPART(DW,@LoopDate)= @SundayDW
begin
set @LoopDate=DATEADD(DAY,@increment,@LoopDate)
end
set @CountDays=@CountDays+1
end

return @LoopDate

END

Tip solutie

Permanent

Follow Us