operaciones - obtener el dia de una fecha sql
Añadir días hábiles hasta la fecha en SQL sin bucles. (22)
Actualmente tengo una función en mi base de datos SQL que agrega una cierta cantidad de días hábiles a una fecha, por ejemplo, si ingresa una fecha que es jueves y agrega dos días, devolverá la fecha del lunes siguiente. No me preocupan las vacaciones, solo se excluyen los fines de semana.
El problema es que esto se realiza actualmente utilizando un bucle while, y parece estar ralentizando enormemente el procedimiento almacenado que lo utiliza al generar una tabla. ¿Alguien sabe si hay alguna manera de realizar este cálculo sin bucles o cursores?
Solo para información, esta es la función actual:
ALTER FUNCTION [dbo].[AddWorkDaysToDate]
(
@fromDate datetime,
@daysToAdd int
)
RETURNS datetime
AS
BEGIN
DECLARE @toDate datetime
DECLARE @daysAdded integer
-- add the days, ignoring weekends (i.e. add working days)
set @daysAdded = 1
set @toDate = @fromDate
while @daysAdded <= @daysToAdd
begin
-- add a day to the to date
set @toDate = DateAdd(day, 1, @toDate)
-- only move on a day if we''ve hit a week day
if (DatePart(dw, @toDate) != 1) and (DatePart(dw, @toDate) != 7)
begin
set @daysAdded = @daysAdded + 1
end
end
RETURN @toDate
END
¿Ha pensado en rellenar previamente una tabla de consulta que contenga todos los días hábiles (usando su función), por ejemplo, WorkingDays (int DaySequenceId, Date WorkingDate), luego puede usar esta tabla seleccionando el DaySequenceId de @fromDate y agregue @daysToAdd para obtener la nueva fecha de trabajo. Obviamente, este método también tiene la sobrecarga adicional de administrar la tabla WorkingDays, pero podría rellenarlo previamente con el rango de fechas que espera. El otro inconveniente es que las fechas de trabajo que se pueden calcular solo serán las que figuran en la tabla WorkingDays.
* Sé que este es un tema antiguo, pero encontré algo extremadamente útil hace un tiempo, lo modifiqué y obtuve esto.
select ((DATEADD(d,DATEDIFF(d,0,(DATEADD (d,2,@fromDate))),@numbOfDays)))*
Actualización: Lamento rápidamente encontrar un fragmento de código (en una sola declaración) y para evitar el uso de una función, publiqué un código incorrecto aquí.
El bit mencionado anteriormente puede usarse si el número de días que está agregando es de 7 o menos.
He cambiado el código con los parámetros requeridos para una mejor comprensión.
De todos modos, terminé usando lo que ''Nate Cook'' ha mencionado anteriormente. Y lo usé como una sola línea de código. (Porque estoy restringiendo el uso de funciones)
Codigo de nate
select(
DATEADD(day, (@days % 5) +
CASE ((@@DATEFIRST + DATEPART(weekday, GETDATE()) + (@days % 5)) % 7)
WHEN 0 THEN 2
WHEN 1 THEN 1
ELSE 0 END, DATEADD(week, (@days / 5), GETDATE()))
)
A partir de la respuesta que se aceptó para esta pregunta, la siguiente función definida por el usuario (UDF) debería funcionar en todos los casos, independientemente de la configuración de @@DateFirst .
ACTUALIZACIÓN: como lo indican los comentarios a continuación, esta función está diseñada para que FromDate sea un día de la semana. El comportamiento no está definido cuando se pasa un día de fin de semana como la Fecha de inicio.
ALTER FUNCTION [dbo].[BusinessDaysDateAdd]
(
@FromDate datetime,
@DaysToAdd int
)
RETURNS datetime
AS
BEGIN
DECLARE @Result datetime
SET @Result = DATEADD(day, (@DaysToAdd % 5) + CASE ((@@DATEFIRST + DATEPART(weekday, @FromDate) + (@DaysToAdd % 5)) % 7)
WHEN 0 THEN 2
WHEN 1 THEN 1
ELSE 0 END, DATEADD(week, (@DaysToAdd / 5), @FromDate))
RETURN @Result
END
Acabo de probar la respuesta aceptada y descubrí que no funciona cuando el domingo es el día de inicio.
Select @Saturday agregar lo siguiente en la línea Select @Saturday :
SELECT @fromDate = CASE WHEN DATEPART(weekday,@fromDate) = 1 THEN DATEADD(day,1,@fromDate) ELSE @fromDate END
Esta función SQL funciona de manera similar a la función WORKDAY de Excel. Espero que te ayude.
CREATE FUNCTION [dbo].[BusDaysDateAdd]
(
@FromDate date,
@DaysToAdd int
)
RETURNS date
AS
BEGIN
DECLARE @Result date
DECLARE @TempDate date
DECLARE @Remainder int
DECLARE @datePartValue int
SET @TempDate = (DATEADD(week, (@DaysToAdd / 5), @FromDate))
SET @Remainder = (@DaysToAdd % 5)
SET @datePartValue = DATEPART(weekday, @TempDate)
SET @Result = DATEADD(day,@Remainder + CASE WHEN @Remainder > 0 AND @datePartValue = 7 THEN 1
WHEN @Remainder >= 1 AND @datePartValue = 6 THEN 2
WHEN @Remainder >= 2 AND @datePartValue = 5 THEN 2
WHEN @Remainder >= 3 AND @datePartValue = 4 THEN 2
WHEN @Remainder >= 4 AND @datePartValue = 3 THEN 2
WHEN @Remainder >= 5 AND @datePartValue = 2 THEN 2
ELSE 0 END, @TempDate)
RETURN @Result
END
GO
Estas respuestas se basan en la respuesta de @ElmerMiller .
Arregla el valor negativo en el comentario del domingo de @FistOfFury
Los valores negativos no funcionan si la fecha pasada es el domingo
Y el comentario de configuración DATEFIRST de @Damien_The_Unbeliever
Pero este asume una configuración DATEFIRST particular (7), que algunos otros no necesitan.
Ahora la función corregida
CREATE FUNCTION[dbo].[AddBusinessDays](@Date DATE,@n INT)
RETURNS DATE AS
BEGIN
DECLARE @d INT,@f INT,@DW INT;
SET @f=CAST(abs(1^SIGN(DATEPART(DW, @Date)-(7-@@DATEFIRST))) AS BIT)
SET @DW=DATEPART(DW,@Date)-(7-@@DATEFIRST)*(@f^1)+@@DATEFIRST*(@f&1)
SET @d=4-SIGN(@n)*(4-@DW);
RETURN DATEADD(D,@n+((ABS(@n)+(@d%(8+SIGN(@n)))-2)/5)*2*SIGN(@n)-@d/7,@Date);
END
Esto es lo que uso:
SET DATEFIRST 1;
SELECT DATEADD(dw, (**NumberToAdd**/5)*7+(**NumberToAdd** % 5) +
(CASE WHEN DATEPART(dw,**YourDate**) + (**NumberToAdd** % 5) > 5
THEN 2 ELSE 0 END), **YourDate**) AS IncrementedDate
FROM YourTable t
El "SET DATEFIRST 1;" La parte es necesaria para establecer el lunes como el primer día de la semana.
Esto es mejor si alguien está buscando una solución TSQL. Sin bucles, sin tablas, sin declaraciones de casos Y funciona con negativos. ¿Alguien puede vencer eso?
CREATE FUNCTION[dbo].[AddBusinessDays](@Date date,@n INT)
RETURNS DATE AS
BEGIN
DECLARE @d INT;SET @d=4-SIGN(@n)*(4-DATEPART(DW,@Date));
RETURN DATEADD(D,@n+((ABS(@n)+@d-2)/5)*2*SIGN(@n)-@d/7,@Date);
END
Gracias Damien por el código. Hubo un ligero error en los cálculos porque se agregó solo 1 día para el domingo, y cuando el número de días hábiles cruzó un fin de semana (pero no aterrizó en el fin de semana), los 2 días adicionales no se tuvieron en cuenta. Aquí hay una versión modificada del código de Damiens que funciona con la fecha predeterminada primero en 7. Espero que esto ayude.
CREATE FUNCTION [dbo].[fn_AddBusinessDays]
(
@StartDate datetime,
@BusinessDays int
)
RETURNS datetime
AS
BEGIN
DECLARE @EndDate datetime
SET @EndDate = DATEADD(day, @BusinessDays%5 +
CASE
WHEN DATEPART(weekday,@StartDate) + @BusinessDays%5 > 6 THEN 2
ELSE 0
END,
DATEADD(week,@BusinessDays/5,@StartDate))
RETURN @EndDate
END
GO
He probado todas las soluciones propuestas aquí y ninguna de ellas funciona. Aquí hay algunos escenarios de prueba que rompieron muchas de las soluciones anteriores. (Suponiendo que sábado y domingo son los días que está excluyendo):
-Agregar 0 días a un sábado - Resultado esperado = sábado
-Agregar 0 días a un domingo - Resultado esperado = domingo
-Agregar 1 día a viernes - Resultado esperado = el lunes siguiente
-Agregar 1 día al sábado - Resultado esperado = el lunes siguiente
-Agregar 1 día al domingo - Resultado esperado = el lunes siguiente
-Agregar 3 días hasta el viernes - Resultado esperado = el miércoles siguiente
-Agregar 5 días al sábado - Resultado esperado = el viernes siguiente
-Agregar 5 días hasta el viernes - Resultado esperado = el viernes siguiente
-Sujete 1 día a partir del lunes - Resultado esperado = el viernes anterior
-Sujete 1 día del domingo - Resultado esperado = el viernes anterior
-Sujetar 1 día del sábado - Resultado esperado = el viernes anterior
-Sujete 3 días a partir del lunes - Resultado esperado = el miércoles anterior
-Sujete 5 días a partir del sábado - Resultado esperado = el lunes anterior
-Sujete 5 días desde el lunes - Resultado esperado = el lunes anterior
Esto es lo que escribí después de leer todo este hilo y escoger las buenas piezas de lógica:
CREATE FUNCTION [dbo].[BusinessDateAdd]
(
@FromDate DATE
,@DaysToAdd INT
)
RETURNS DATE
AS
BEGIN
--If there are no days to add or subtract, return the day that was passed in
IF @DaysToAdd = 0 RETURN @FromDate
DECLARE @Weeks INT
DECLARE @DMod INT
DECLARE @FromDateIndex INT
--number of weeks
SET @Weeks = @DaysToAdd/5
--remainder of days
SET @dmod = @DaysToAdd%5
--Get the FromDate day of the week, this logic standardizes the @@DateFirst to Sunday = 1
SET @FromDateIndex = (DATEPART(weekday, @FromDate) + @@DATEFIRST - 1) % 7 + 1
/*Splitting the addition vs subtraction logic for readability*/
--Adding business days
IF @DaysToAdd > 0
BEGIN
--If the FromDate is on a weekend, move it to the previous Friday
IF @FromDateIndex IN(1,7)
BEGIN
SET @FromDate = DATEADD(dd,CASE @FromDateIndex WHEN 1 THEN -2 WHEN 7 THEN -1 END,@FromDate)
SET @FromDateIndex = 6
END
SET @FromDate = DATEADD(dd,
CASE
--If the mod goes through the weekend, add 2 days to account for it
WHEN
((@FromDateIndex = 3 --Tuesday
AND @dmod > 3) --Days until Friday
OR
(@FromDateIndex = 4 --Wednesday
AND @dmod > 2)--Days until Friday
OR
(@FromDateIndex = 5 --Thursday
AND @dmod > 1)--Days until Friday
OR
(@FromDateIndex = 6 --Friday
AND @dmod > 0))--Days until Friday
THEN
@DMod+2
--Otherwise just add the mod
ELSE
@DMod
END, @FromDate)
END
--Subtracting business days
IF @DaysToAdd < 0
BEGIN
--If the FromDate is on a weekend, move it to the next Monday
IF @FromDateIndex IN(1,7)
BEGIN
SET @FromDate = DATEADD(dd,CASE @FromDateIndex WHEN 1 THEN 1 WHEN 7 THEN 2 END,@FromDate)
SET @FromDateIndex = 2
END
SET @FromDate = DATEADD(dd,
CASE
--If the mod goes through the weekend, subtract 2 days to account for it
WHEN
((@FromDateIndex = 5 --Thursday
AND @dmod < -3) --Days until Monday
OR
(@FromDateIndex = 4 --Wednesday
AND @dmod < -2)--Days until Monday
OR
(@FromDateIndex = 3 --Tuesday
AND @dmod < -1)--Days until Monday
OR
(@FromDateIndex = 2 --Monday
AND @dmod < 0))--Days until Monday
THEN
@DMod-2
--Otherwise just subtract the mod
ELSE
@DMod
END, @FromDate)
END
--Shift the date by the number of weeks
SET @FromDate = DATEADD(ww,@Weeks,@FromDate)
RETURN @FromDate
END
La matemática típica de agregar semanas y días restantes no funciona bien con la suma / resta de días de la semana en general. Es por esto que el método de bucle para agregar los días restantes es fácil de implementar. La parte de bucle de la lógica está diseñada para manejar estos casos especiales de fin de semana.
Sin el bucle, tendrá que identificar las condiciones especiales para compensar con algún tipo de compensación después de agregar semanas y días restantes. El valor de compensación también depende de la dirección del delta (positiva o negativa).
Además, necesitará una lógica que pueda evaluar constantemente si una fecha cae en un día de la semana o en un fin de semana con cualquier valor @@ DATEFIRST.
Casos de uso:
- Delta es cero.
- La fecha de entrada es fin de semana y el resto = 0.
- La fecha de entrada es el fin de semana y el resto! = 0 y (la fecha de entrada es Sat & delta es positiva; o la fecha de entrada es Sun y delta es negativa).
- La fecha de entrada es el día de la semana y el ajuste restante hace que la fecha de salida caiga en un fin de semana o cruza un fin de semana en comparación con la fecha de entrada.
- La fecha de entrada es el día de la semana y el ajuste del resto no hace que la fecha de salida caiga en un fin de semana ni cruza un fin de semana en comparación con la fecha de entrada.
La lógica de trabajo principal para encontrar la fecha de salida será, sumar / restar por número de días:
Output = Input + (Weeks * 7) + Days
Donde: semanas = int (delta / 5), días = delta% 5
En el caso número 1, devolvemos la fecha de entrada ya que no se necesitan cálculos (se supone que ese es el requisito). Los casos de uso # 2-4 son casos de uso especiales a los que tenemos que aplicar un desplazamiento para corregir la fecha de salida. Para el # 5 no hay compensación necesaria. Por lo tanto se convertirá en:
Output = Input + (Weeks * 7) + Days + Offset
Como máximo, nuestro rango de valores de compensación está entre 2 y -2 porque hay dos días de fin de semana.
Usando DATEPART (), nos adheriremos a la regla de 0 = domingo, 6 = sábado, 1-5 = lunes a viernes. Usaremos @@ DATEFIRST para ayudar a compensar y mantener los valores insistentes.
Guión con / Comentarios:
DECLARE
@date DATE = ''2018-04-25'',
@n INT = 5 -- delta
IF @n != 0 -- (opposite of use case #1)
BEGIN
DECLARE @dw TINYINT = (DATEPART(DW, @date) + @@DATEFIRST - 1) % 7,
@offset INT = 0
IF @dw IN (0,6) -- if start = weekend (use case #2 & #3)
BEGIN
IF @n > 0 -- pos delta
BEGIN
IF @n%5 = 0 -- weekend start, weekly adjustment only
SELECT @offset = CASE WHEN @dw = 0 THEN -2 ELSE -1 END
ELSE IF @dw = 6 -- sat start, offset to sun
SELECT @offset = 1
END
ELSE -- neg delta
BEGIN
IF @n%5 = 0 -- weekend start, weekly adjustment only
SELECT @offset = CASE WHEN @dw = 0 THEN 1 ELSE 2 END
ELSE IF @dw = 0 -- sun start, offset to sat
SELECT @offset = -1
END
END
ELSE -- otherwise, start = weekday (use case #4)
BEGIN
DECLARE @dw2 TINYINT = (@dw + (@n%5) + 7) % 7
-- if result is/crosses weekend
IF @dw2 IN (0,6) OR (@n > 0 AND @dw2 < @dw) OR (@n < 0 AND @dw < @dw2)
SET @offset = CASE WHEN @n > 0 THEN 2 ELSE -2 END
END
-- adjust date by weeks + remainder + offset (if @offset = 0, use case #5)
SET @date = DATEADD(D, (@n%5) + @offset, DATEADD(D, CAST(@n/5 AS INT) * 7, @date))
END
SELECT @date
Guión Compacto
DECLARE
@date DATE = ''2018-04-27'',
@n INT = 3
IF @n != 0
BEGIN
DECLARE @dw TINYINT = (DATEPART(DW, @date) + @@DATEFIRST - 1) % 7, @d INT = @n % 5
DECLARE @dw2 TINYINT = (@dw + @d + 7) % 7
SET @date = DATEADD(D, @d + ISNULL(CASE
WHEN @dw IN (0,6) THEN
CASE WHEN @n > 0
THEN CASE WHEN @d = 0 THEN CASE WHEN @dw = 0 THEN -2 ELSE -1 END WHEN @dw = 6 THEN 1 END
ELSE CASE WHEN @d = 0 THEN CASE WHEN @dw = 0 THEN 1 ELSE 2 END WHEN @dw = 0 THEN -1 END
END
WHEN @dw2 IN (0,6) OR (@n > 0 AND @dw2 < @dw) OR (@n < 0 AND @dw < @dw2) THEN
CASE WHEN @n > 0 THEN 2 ELSE -2 END
END, 0), DATEADD(D, (@n / 5)* 7, @date))
END
SELECT @date
La respuesta aceptada de la pregunta produce resultados incorrectos. Por ejemplo, select @fromDate = ''03-11-1983'', @DaysToAdd = 3 da como resultado 03-14-1983 mientras que 03-16-1983 se expected .
Publiqué una solución de trabajo here , pero para completar, también la agregaré aquí. Si está interesado en los detalles de los dos métodos, visite mi respuesta original. Si no, simplemente copie / pegue esto en su proyecto SQL y use UTL_DateAddWorkingDays
Tenga en cuenta que mi solución solo funciona si DATEFIRST se establece en el valor predeterminado de 7.
Script de prueba utilizado para probar varios métodos
CREATE FUNCTION [dbo].[UTL_DateAddWorkingDays]
(
@date datetime,
@days int
)
RETURNS TABLE AS RETURN
(
SELECT
CASE
WHEN @days = 0 THEN @date
WHEN DATEPART(dw, @date) = 1 THEN (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](DATEADD(d, 1, @date), @days - 1))
WHEN DATEPART(dw, @date) = 7 THEN (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](DATEADD(d, 2, @date), @days - 1))
ELSE (SELECT Date FROM [dbo].[UTL_DateAddWorkingDays_Inner](@date, @days))
END AS Date
)
CREATE FUNCTION [dbo].[UTL_DateAddWorkingDays_Inner]
(
@date datetime,
@days int
)
RETURNS TABLE AS RETURN
(
SELECT
DATEADD(d
, (@days / 5) * 7
+ (@days % 5)
+ (CASE WHEN ((@days%5) + DATEPART(dw, @date)) IN (1,7,8,9,10) THEN 2 ELSE 0 END)
, @date) AS Date
)
Llego un poco tarde a esta fiesta, pero terminé escribiendo mi propia versión de esto, debido a inconvenientes en las otras soluciones. Específicamente, esta versión trata el conteo hacia atrás y comienza los fines de semana.
Existe una situación ambigua que podría surgir si agrega cero días hábiles a una fecha de fin de semana. He mantenido la misma fecha, pero puede omitir esta comprobación si siempre desea forzar la devolución de un día de la semana.
CREATE FUNCTION [dbo].[fn_AddBusinessDays]
(
@date datetime,
@businessDays int
)
RETURNS datetime
AS
BEGIN
--adjust for weeks first
declare @weeksToAdd int = @businessDays / 7
declare @daysToAdd int = @businessDays % 7
--if subtracting days, subtract a week then offset
if @businessDays < 0 begin
set @daysToAdd = @businessDays + 5
set @weeksToAdd = @weeksToAdd - 1
end
--saturday becomes zero using the modulo operator
declare @originalDayOfWeek int = datepart(dw, @date) % 7
declare @newDayOfWeek int = datepart(dw, dateadd(d, @daysToAdd, @date)) % 7
--special case for when beginning date is weekend
--adding zero on a weekend keeps the same date. you can remove the <> 0 check if you want Sunday + 0 => Monday
declare @dateOffset int = case
when @businessDays <> 0 and @originalDayOfWeek = 0 then 2
when @businessDays <> 0 and @originalDayOfWeek = 1 then 1
when @businessDays <> 0 and @newDayOfWeek < @originalDayOfWeek then 2
else 0
end
-- Return the result of the function
return dateadd(d, @daysToAdd + @dateOffset, dateadd(ww, @weeksToAdd, @date))
END
No tengo Sql Server en el momento de la prueba, pero esta es la idea:
ALTER FUNCTION [dbo].[AddWorkDaysToDate]
(
@fromDate datetime,
@daysToAdd int
)
RETURNS datetime
AS
BEGIN
DECLARE @dw integer
DECLARE @toDate datetime
set datefirst 1
set @toDate = dateadd(day, @daysToAdd, @fromDate)
set @dw = datepart(dw, @toDate)
if @dw > 5 set @toDate = dateadd(day, 8 - @dw, @toDate)
RETURN @toDate
END
Para Alemania TODAS las respuestas NO FUNCIONAN.
La única función que probé y funciona es una traducción de un antiguo formulario de excel en http://ms-excel.eu/formeln/beispiel-formeln-datum/excel-datum-plus-arbeitstage.html :
Set @EndDate=Dateadd(DAY,@DaysToAdd,@FromDate) +
Cast(((
CASE WHEN 5 <= DATEPART(weekday, @FromDate)%7
THEN 5
ELSE
DATEPART(weekday, @FromDate)%7
END)
-1 + @DaysToAdd )/5
as int)
* 2 -
(Case when DAtepart(weekday, @FromDate)=6 then 1 else 0 end)
Para ampliar el comentario de Amine y la respuesta de Nate Cook arriba, la solución de una sola línea para esto es:
declare @DaysToAdd int , @FromDate datetime
set @DaysToAdd=-5 --5 days prior is 3/28/14
set @FromDate=''4/4/14''
select
DATEADD(day, (@DaysToAdd % 5)
+ CASE
WHEN ((@@DATEFIRST + DATEPART(weekday, @FromDate)) % 7 + (@DaysToAdd % 5)) > 6 THEN 2
ELSE 0
END
, DATEADD(week, (@DaysToAdd / 5), @FromDate))
Tenga en cuenta que puede agregar o restar días para avanzar y retroceder en el tiempo, respectivamente.
Sé que es un poco tarde, tal vez alguien más tropieza con este problema. He intentado la solución anterior, pero la mayoría de ellos no pueden calcular las vacaciones.
Así es como lo intenté.
CREATE function [dbo].[DateAddWorkDay]
(@days int,@FromDate Date)
returns Date
as
begin
declare @result date
set @result = (
select b
from
(
SELECT
b,
(DATEDIFF(dd, a, b))
-(DATEDIFF(wk, a, b) * 2)
-(CASE WHEN DATENAME(dw, a) = ''Sunday'' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, b) = ''Saturday'' THEN 1 ELSE 0 END)
-COUNT(o.Holiday_Date)
as workday
from
(
select
@FromDate as a,
dateadd(DAY,num +@days,@FromDate) as b
from (select row_number() over (order by (select NULL)) as num
from Information_Schema.columns
) t
where num <= 100
) dt
left join Holiday o on o.Holiday_Date between a and b and DATENAME(dw, o.Holiday_Date) not in(''Saturday'',''Sunday'')
where DATENAME(dw, b) not in(''Saturday'',''Sunday'')
and b not in (select Holiday_Date from OP_Holiday where Holiday_Date between a and b)
group by a,b
) du
where workday =@days
)
return @result
end
Donde Holiday es una tabla con holiday_date como referencia para vacaciones
Espero que esto pueda ayudar a alguién.
Suspiro. No puedo creer que después de todas estas décadas todavía no haya: a) "DateAddWorkDays" estándar en Microsoft SQL Server (a pesar de que Microsoft ha tenido una función WorkDay en Excel para siempre) yb) solución clara aquí o en cualquier otro lugar. Maneja todos los problemas que la gente ha planteado.
Aquí hay una solución que desarrollé que aborda los siguientes problemas que, aparentemente, todas las respuestas anteriores aquí y en otros lugares que he podido encontrar tienen uno o más. Esto maneja:
- Nombres identificadores mnemotécnicos.
- Comentarios explicando el código que no está claro.
- No verificar cada día de trabajo que necesite incrementarse (es decir, mucho menos que la complejidad O (n)).
- Incrementos de jornada laboral negativa.
- Permitiendo que la parte de tiempo que no sea a las 12 am se pase (para que no tenga que quitarla primero).
- Retener la porción de tiempo pasada, si la hubiera, en el resultado (en caso de que necesite la hora exacta con x días hábiles de anticipación / anterior).
- Nombres de los días de fin de semana en otros idiomas además del inglés.
- @@ DateFirst valores distintos de los predeterminados (7 alias US).
- Especificando una lista personalizada de días no laborables no de fin de semana.
- Permitir que la lista de días no laborables que no sean fines de semana de trabajo si la fecha de vencimiento tiene un tiempo no de 12 am.
- Regreso a la fecha y hora de inicio si # el incremento de días laborales es 0, incluso si la fecha y hora de inicio es un día no laborable.
- Pasar al día laborable siguiente / anterior primero antes de comenzar a aumentar / disminuir días laborables, respectivamente. NOTA: Esto difiere de la función
WorkDayde Excel, pero creo que es más útil e intuitivo. Ex. Si recibe una consulta / pedido en un día de fin de semana y tiene un SLA (es decir, tiempo de respuesta, fecha de entrega) de 1 día hábil, no debería tener que responder / entregar hasta que haya transcurrido 1 día hábil completo (independientemente de cómo muchos días no laborables adyacentes lo precedieron). - Omitir los fines de semana adicionales y / o los días de semana no laborables que se hayan extendido después de agregar cualquier día de la semana no laborable que se haya extendido al agregar los fines de semana iniciales al agregar el número de días laborables solo, y repetir hasta que ya no sea necesario.
SUGERENCIAS: Por supuesto, como con cualquier algoritmo recursivo, este se puede convertir a uno iterativo (implementando su propia pila, es decir, con una tabla de temperatura), pero creo que los 32 niveles de anidación son más que suficientes para la gran mayoría de casos de uso del mundo real. Además, por supuesto, puede hacer que sea más genérico / portátil pasando las fechas de los días laborables que no funcionan como un parámetro de valor de tabla en lugar de una referencia de tabla codificada.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================================================================================================
-- Author: Tom
-- Create date: 03/13/2017
-- Description: Add specified # of working days (+/-) to a specified date-time assuming existence of a list of non-work weekday
-- dates (incl. holidays, weather days, utility outage days, fire days, etc.) in the ''NonWorkDayDate'' Column of a ''NonWorkWeekday''
-- Table. If specified # working days is 0, the specified date-time is returned. Working days are not added until the specified
-- date-time has first been incremented (+/-) to the next working day in the direction of the working days increment.
-- NOTE: Uses a forumla (vs. O(n) loop) that uses recusion whenever days incremented (incl. weekends) spans non-work weekdays.
-- !!!WARNING!!!: Will exceed SQL Server nesting level (32) if abs (# of working days) < ~1 / 32 adjacent non-working days.
-- Parameters:
-- @RefDateTime DateTime: Reference date-time to which to add ''@WorkDaysIncrement''.
-- @WorkDaysIncrement Int: # of working days (+/-) to add # to the ''@RefDateTime''.
-- Returns:
-- 1. Result of @RefDateTime + @WorkDaysIncrement (skipping weekend and holiday dates and retaining the @RefDateTime''s time).
-- ===================================================================================================================================
CREATE FUNCTION [dbo].[AddWorkDays_Recursive]
(
-- Add the parameters for the function here
@RefDateTime datetime,
@WorkDaysIncrement int
)
RETURNS DateTime
AS
BEGIN
-- If no days to increment, return passed in date-time (even if weekend day).
if (@WorkDaysIncrement = 0) return @RefDateTime
-- Set the one-day increment used to add or subtract one calendar/work day.
declare @OneDayIncrement int = sign(@WorkDaysIncrement)
-- Initialize # of calendar days added to 0.
declare @DaysAdded int = 0
-- Set reference date to date (i.e. excl. time) of reference date-time.
declare @RefDate datetime = convert
(
date,
convert
(
varchar(10),
@RefDateTime,
101
)
)
--end declare @RefDate
-- Initialize result date to reference date
declare @ResultDate datetime = @RefDate
-- Set U.S. Weekday # to the 1-based U.S. weekday # result date.
declare @USWeekdayNumber tinyint = ((datepart(weekday, @ResultDate) + @@datefirst - 1) % 7) + 1 -- Sun to Sat = 1 to 7
-- If result date is now on a weekend day, set # of weekend days increment so that we can move it +/- 1 to 2 days to next weekday.
declare @WeekendDaysInc smallint =
(
case (@USWeekdayNumber)
when 1 then --Sunday
case
when (@OneDayIncrement > 0) then 1
else -2
end
--end when 1 --Sunday
when 7 then --Saturday
case
when (@OneDayIncrement > 0) then 2
else -1
end
--end when 7 then --Saturday
else 0 -- Not Weekend Day #
end -- case (@USWeekdayNumber)
) -- end declare @WeekendDaysInc smallint =
-- Increment # of calendar days added by # of weekend days increment
set @DaysAdded += @WeekendDaysInc
-- Increment result date by # of weekend days increment
set @ResultDate += @WeekendDaysInc
-- Set # of work weeks increment to # of full 5-day increments in the # (+/-) of work days to increment.
declare @WorkWeeksIncrement int = @WorkDaysIncrement / 5
-- Increment # of calendar days added by 7 times # of work weeks increment, i.e. to add weekday + weekend days for full weeks.
set @DaysAdded += @WorkWeeksIncrement * 7
-- Set result date after full weeks added to reference date + # of calendar days
declare @AfterFullWeeksResultDate datetime = @ResultDate + @DaysAdded
-- Set # partial-work week days to # (+/-) of work days to increment left after adding full weeks.
declare @PartialWorkWeekDays int = @WorkDaysIncrement % 5
-- Increment # of calendar days added by # partial-work week days
set @DaysAdded += @PartialWorkWeekDays
-- Set result date after partial week added to result date after full weeks added + # partial work week days
declare @AfterPartialWeekResultDate datetime = @AfterFullWeeksResultDate + @PartialWorkWeekDays
--Set result date to result date after partial week.
set @ResultDate = @AfterPartialWeekResultDate
-- Set After Full Weeks U.S. Weekday # to the 1-based U.S. weekday # result date.
declare @AfterFullWeeksUSWeekdayNumber tinyint =
(
((datepart(weekday, @AfterFullWeeksResultDate) + @@datefirst - 1) % 7) + 1 -- Sun to Sat = 1 to 7
)
-- Set After Partial Week U.S. Weekday # to the 1-based U.S. weekday # result date.
declare @AfterPartialWeekUSWeekdayNumber tinyint =
(
((datepart(weekday, @AfterPartialWeekResultDate) + @@datefirst - 1) % 7) + 1 -- Sun to Sat = 1 to 7
)
--If (incrementing and After Full Weeks U.S. Weekday # > @AfterPartialWeekUSWeekdayNumber)
-- or (decrementing and After Full Weeks U.S. Weekday # < @AfterPartialWeekUSWeekdayNumber), increment by (+/-) 2 to account for
-- the weekend that was spanned when partial-work week days were added.
if
(
(
(@OneDayIncrement > 0)
and (@AfterFullWeeksUSWeekdayNumber > @AfterPartialWeekUSWeekdayNumber)
)
or (
(@OneDayIncrement < 0)
and (@AfterFullWeeksUSWeekdayNumber < @AfterPartialWeekUSWeekdayNumber)
)
)
begin
set @WeekendDaysInc = 2 * @OneDayIncrement
set @DaysAdded += @WeekendDaysInc
set @ResultDate += @WeekendDaysInc
end -- if need to increment to account for weekend spanned by partial-work week days,
-- Set U.S. Weekday # to the 1-based U.S. weekday # result date.
set @USWeekdayNumber = ((datepart(weekday, @ResultDate) + @@datefirst - 1) % 7) + 1 -- Sun to Sat = 1 to 7
-- If result date is now on a weekend day, set # of weekend days increment so that we can move it +/- 1 to 2 days to next weekday.
set @WeekendDaysInc =
(
case (@USWeekdayNumber)
when 1 then --Sunday
case
when (@OneDayIncrement > 0) then 1
else -2
end
--end when 1 --Sunday
when 7 then --Saturday
case
when (@OneDayIncrement > 0) then 2
else -1
end
--end when 7 then --Saturday
else 0 -- Not Weekend Day #
end -- case (@USWeekdayNumber)
) -- end declare @WeekendDaysInc smallint =
-- Increment # of calendar days added by # of weekend days increment
set @DaysAdded += @WeekendDaysInc
-- Increment result date by # of weekend days increment
set @ResultDate += @WeekendDaysInc
-- Set non-work weedays count to # Rows where NonWorkDayDate between RefDate and ResultDate (if # of work days to increment > 0), else between
-- ResultDate and RefDate.
declare @NonWorkWeekdaysCount int =
(
select count(nw.NonWorkDayDate)
from NonWorkWeekday as nw
where
(
(@OneDayIncrement > 0)
and (nw.NonWorkDayDate between @RefDate and @ResultDate)
)
or (
(@OneDayIncrement < 0)
and (nw.NonWorkDayDate between @ResultDate and @RefDate)
)
--end select count(nw.NonWorkDayDate) from Holidate as nw
) -- end declare @HolidaysSpanned int =
-- Set result date-time to reference date-time + # of calendar days added
declare @ResultDateTime datetime = @RefDateTime + @DaysAdded
-- Set result date-time equal to result of adding (# of holidays x one-day increment).
set @ResultDateTime = dbo.AddWorkDays_Recursive
(
@ResultDateTime, -- @RefDateTime
@NonWorkWeekdaysCount * @OneDayIncrement -- @WorkDaysIncrement
)
--end set @ResultDateTime =
-- Return the result of the function
RETURN @ResultDateTime
END
GO
Esta respuesta ha sido alterada significativamente desde que fue aceptada, ya que el original estaba equivocado. Sin embargo, tengo más confianza en la nueva consulta y no depende de DATEFIRST
Creo que esto debería cubrirlo:
declare @fromDate datetime
declare @daysToAdd int
select @fromDate = ''20130123'',@DaysToAdd = 4
declare @Saturday int
select @Saturday = DATEPART(weekday,''20130126'')
;with Numbers as (
select 0 as n union all select 1 union all select 2 union all select 3 union all select 4
), Split as (
select @DaysToAdd%5 as PartialDays,@DaysToAdd/5 as WeeksToAdd
), WeekendCheck as (
select WeeksToAdd,PartialDays,MAX(CASE WHEN DATEPART(weekday,DATEADD(day,n.n,@fromDate))=@Saturday THEN 1 ELSE 0 END) as HitWeekend
from
Split t
left join
Numbers n
on
t.PartialDays >= n.n
group by WeeksToAdd,PartialDays
)
select DATEADD(day,WeeksToAdd*7+PartialDays+CASE WHEN HitWeekend=1 THEN 2 ELSE 0 END,@fromDate)
from WeekendCheck
Dividimos el tiempo que se agregará en un número de semanas y un número de días dentro de una semana. Luego usamos una tabla de números pequeños para calcular si agregar esos pocos días resultará en que lleguemos a un sábado. Si lo hace, entonces necesitamos agregar 2 días más sobre el total.
Recientemente resolví este problema para agregar dos días hábiles a la fecha actual mediante la creación de un valor INT @DaysToAdd: probado y funcionando muy bien en 2008/2012.
DECLARE @DaysToAdd INT
SELECT @DaysToAdd = CASE
WHEN DATEPART(WEEKDAY,GETDATE()) = 1 THEN 3 -- Sunday -> Wednesday
WHEN DATEPART(WEEKDAY,GETDATE()) = 5 THEN 4 -- Thursday -> Monday
WHEN DATEPART(WEEKDAY,GETDATE()) = 6 THEN 4 -- Friday -> Tuesday
WHEN DATEPART(WEEKDAY,GETDATE()) = 7 THEN 4 -- Saturday -> Wednesday
ELSE 2 END
SELECT DATEADD(DAY, @DaysToAdd, GETDATE()) AS TwoWorkingDaysTime
CREATE FUNCTION DateAddBusinessDays
(
@Days int,
@Date datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @DayOfWeek int;
SET @DayOfWeek = CASE
WHEN @Days < 0 THEN (@@DateFirst + DATEPART(weekday, @Date) - 20) % 7
ELSE (@@DateFirst + DATEPART(weekday, @Date) - 2) % 7
END;
IF @DayOfWeek = 6 SET @Days = @Days - 1
ELSE IF @DayOfWeek = -6 SET @Days = @Days + 1;
RETURN @Date + @Days + (@Days + @DayOfWeek) / 5 * 2;
END;
Esta función puede agregar y restar días hábiles independientemente del valor de @@ DATEFIRST. Para restar días hábiles use un número negativo de días.
WITH get_dates
AS
(
SELECT getdate() AS date, 0 as DayNo
UNION ALL
SELECT date + 1 AS date, case when DATEPART(DW, date + 1) IN (1,7) then DayNo else DayNo + 1 end
FROM get_dates
WHERE DayNo < 4
)
SELECT max(date) FROM get_dates
OPTION (MAXRECURSION 0)