Situatie
Mai jos este o funcite ce returneaza diferenta in zile dintre doua date calendaristice fara sa tina cont de weekend-uri.
Solutie
— select dbo.fn_WorkDays_fct ( ‘20211011’, ‘20211018’ )
alter FUNCTION dbo.fn_WorkDays_fct
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Swap DATETIME
IF @StartDate IS NULL
RETURN NULL
IF @EndDate IS NULL
SELECT @EndDate = @StartDate
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
@EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate) , 0)
IF @StartDate > @EndDate
SELECT @Swap = @EndDate,
@EndDate = @StartDate,
@StartDate = @Swap
RETURN (
SELECT
(DATEDIFF(dd,@StartDate, @EndDate)+1)
-(DATEDIFF(wk,@StartDate, @EndDate)*2)
-(CASE WHEN DATENAME(dw, @StartDate) = ‘Sunday’
THEN 1
ELSE 0
END)
-(CASE WHEN DATENAME(dw, @EndDate) = ‘Saturday’
THEN 1
ELSE 0
END)
)
END
GO
Leave A Comment?