Diferenta intre doua date in zile lucratoare

Configurare noua (How To)

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

Tip solutie

Permanent
Etichetare:

Voteaza

(1 din 10 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?