Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Null on Null Input | False |
| Schema Bound | False |
| Assembly |
Parameters
3| Name | Data Type | Length | Description |
|---|---|---|---|
| (Result) | date | 3 | |
| @DATE | date | 3 | |
| @NDAYS | int | 4 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE OR ALTER FUNCTION dbo.DiasHabilesADD (@DATE DATE,@NDAYS INT)
returns DATE
BEGIN
/*
select dbo.BusinessDays('2021-11-01','2021-11-30')
select dbo.DiasHabilesADD('2021-12-01',5)
select DATEPART(dw,getdate()-1) as weekday
*/
IF @DATE IS NULL
BEGIN
RETURN NULL;
END
DECLARE @STARTDATE INT = 0
DECLARE @COUNT INT = 0
DECLARE @NEWDATE DATE=Dateadd(day, 1, @DATE)
WHILE @COUNT < @NDAYS
BEGIN
IF Datepart(weekday, @NEWDATE) NOT IN (1) /* excluímos los domingos*/
AND @NEWDATE NOT IN (SELECT fecha_feriado FROM feriados) /* excluímos los feriados*/
SET @COUNT += 1;
SELECT @NEWDATE = Dateadd(day, 1, @NEWDATE),
@STARTDATE += 1;
END
RETURN Dateadd(day, @STARTDATE, @DATE);
END
GO
GO
CREATE OR ALTER FUNCTION dbo.DiasHabilesADD (@DATE DATE,@NDAYS INT)
returns DATE
BEGIN
/*
select dbo.BusinessDays('2021-11-01','2021-11-30')
select dbo.DiasHabilesADD('2021-12-01',5)
select DATEPART(dw,getdate()-1) as weekday
*/
IF @DATE IS NULL
BEGIN
RETURN NULL;
END
DECLARE @STARTDATE INT = 0
DECLARE @COUNT INT = 0
DECLARE @NEWDATE DATE=Dateadd(day, 1, @DATE)
WHILE @COUNT < @NDAYS
BEGIN
IF Datepart(weekday, @NEWDATE) NOT IN (1) /* excluímos los domingos*/
AND @NEWDATE NOT IN (SELECT fecha_feriado FROM feriados) /* excluímos los feriados*/
SET @COUNT += 1;
SELECT @NEWDATE = Dateadd(day, 1, @NEWDATE),
@STARTDATE += 1;
END
RETURN Dateadd(day, @STARTDATE, @DATE);
END
GO