por - Cálculo del número de meses completos entre dos fechas en SQL

operaciones con fechas sql

Question

Necesito calcular el número de meses COMPLETOS en SQL, es decir,

• 2009-04-16 a 2009-05-15 => 0 mes completo
• 2009-04-16 a 2009-05-16 => 1 mes completo
• 2009-04-16 a 2009-06-16 => 2 meses completos

Traté de usar DATEDIFF, es decir,

SELECT DATEDIFF(MONTH, ''2009-04-16'', ''2009-05-15'')

pero en lugar de darme meses completos entre las dos fechas, me da la diferencia de la parte del mes, es decir,

1

¿Alguien sabe cómo calcular la cantidad de meses completos en SQL Server?

Answer 1

¿Cuál es tu definición de un mes? Técnicamente, un mes puede ser 28,29,30 o 31 días dependiendo del mes y los años bisiestos.

Parece que está considerando un mes para ser de 30 días ya que en su ejemplo descartó que mayo tenga 31 días, entonces ¿por qué no hacer lo siguiente?

SELECT DATEDIFF(DAY, ''2009-04-16'', ''2009-05-15'')/30
, DATEDIFF(DAY, ''2009-04-16'', ''2009-05-16'')/30
, DATEDIFF(DAY, ''2009-04-16'', ''2009-06-16'')/30

Answer 2

Busqué en internet. Y la sugerencia que encontré es agregar +1 al final.

Intenta hacerlo así:

Declare @Start DateTime
Declare @End DateTime
Set @Start = ''11/1/07''
Set @End = ''2/29/08''
Select DateDiff(Month, @Start, @End + 1)

Answer 3

DATEDIFF () está diseñado para devolver los límites numéricos cruzados entre las dos fechas para el lapso especificado. Para que haga lo que desea, necesita hacer un ajuste adicional para tener en cuenta cuando las fechas cruzan un límite pero no completan el lapso completo.

Answer 4

Esta respuesta sigue el formato T-SQL. Conceptualizo este problema como una de una distancia de tiempo lineal entre dos puntos de fecha en formato de fecha y hora , llamándolos Tiempo1 y Tiempo2; Time1 debe estar alineado con el valor ''anterior en el tiempo'' con el que está tratando (por ejemplo, una fecha de nacimiento o una fecha de creación de widget o una fecha de inicio de viaje) y Time2 debe estar alineado con el valor ''más nuevo en el tiempo'' (por ejemplo, una fecha de instantánea o una fecha de finalización de widget o una fecha de punto de control de viaje).

DECLARE @Time1 DATETIME
SET @Time1 = ''12/14/2015''
DECLARE @Time2 DATETIME
SET @Time2 = ''12/15/2016''

La solución aprovecha medidas simples, conversiones y cálculos de las intersecciones seriales de ciclos múltiples de diferentes longitudes; aquí: Siglo, Década, Año, Mes, Día (¡gracias calendario maya por el concepto!). Una rápida nota de agradecimiento: agradezco a otros colaboradores de por mostrarme algunas de las funciones de los componentes en este proceso que he cosido. He valorado positivamente estos en mi tiempo en este foro.

Primero, construya un horizonte que sea el conjunto lineal de las intersecciones de los ciclos Siglo, Década, Año, Mes, incrementales por mes. Use la función cartesiana de unión cruzada para esto. (Piense en esto como crear la tela desde la cual cortaremos una longitud entre dos puntos ''yyyy-mm'' para medir la distancia):

SELECT
Linear_YearMonths = (centuries.century + decades.decade + years.[year] + months.[Month]),
1 AS value
INTO #linear_months
FROM
(SELECT ''18'' [century] UNION ALL
SELECT ''19'' UNION ALL
SELECT ''20'') centuries
CROSS JOIN
(SELECT ''0'' [decade] UNION ALL
SELECT ''1'' UNION ALL
SELECT ''2'' UNION ALL
SELECT ''3'' UNION ALL
SELECT ''4'' UNION ALL
SELECT ''5'' UNION ALL
SELECT ''6'' UNION ALL
SELECT ''7'' UNION ALL
SELECT ''8'' UNION ALL
SELECT ''9'') decades
CROSS JOIN
(SELECT ''1'' [year] UNION ALL
SELECT ''2'' UNION ALL
SELECT ''3'' UNION ALL
SELECT ''4'' UNION ALL
SELECT ''5'' UNION ALL
SELECT ''6'' UNION ALL
SELECT ''7'' UNION ALL
SELECT ''8'' UNION ALL
SELECT ''9'' UNION ALL
SELECT ''0'') years
CROSS JOIN
(SELECT ''-01'' [month] UNION ALL
SELECT ''-02'' UNION ALL
SELECT ''-03'' UNION ALL
SELECT ''-04'' UNION ALL
SELECT ''-05'' UNION ALL
SELECT ''-06'' UNION ALL
SELECT ''-07'' UNION ALL
SELECT ''-08'' UNION ALL
SELECT ''-09'' UNION ALL
SELECT ''-10'' UNION ALL
SELECT ''-11'' UNION ALL
SELECT ''-12'') [months]
ORDER BY 1

Luego, convierta sus puntos de fecha Time1 y Time2 en el formato ''aaaa-mm'' (piense en estos como puntos de corte coordinados en toda la tela). Conserva las versiones originales de fecha y hora de los puntos:

SELECT
Time1 = @Time1,
[YYYY-MM of Time1] = CASE
WHEN LEFT(MONTH(@Time1),1) <> ''1'' OR MONTH(@Time1) = ''1''
THEN (CAST(YEAR(@Time1) AS VARCHAR) + ''-'' + ''0'' + CAST(MONTH(@Time1) AS VARCHAR))
ELSE (CAST(YEAR(@Time1) AS VARCHAR) + ''-'' + CAST(MONTH(@Time1) AS VARCHAR))
END,
Time2 = @Time2,
[YYYY-MM of Time2] = CASE
WHEN LEFT(MONTH(@Time2),1) <> ''1'' OR MONTH(@Time2) = ''1''
THEN (CAST(YEAR(@Time2) AS VARCHAR) + ''-'' + ''0'' + CAST(MONTH(@Time2) AS VARCHAR))
ELSE (CAST(YEAR(@Time2) AS VARCHAR) + ''-'' + CAST(MONTH(@Time2) AS VARCHAR))
END
INTO #datepoints

Luego, seleccione la distancia ordinal de las unidades ''aaaa-mm'', menos una para convertir a distancia cardinal (es decir, corte un trozo de tela de toda la tela en los puntos de corte identificados y obtenga su medición sin procesar):

SELECT
d.*,
Months_Between = (SELECT (SUM(l.value) - 1) FROM #linear_months l
WHERE l.[Linear_YearMonths] BETWEEN d.[YYYY-MM of Time1] AND d.[YYYY-MM of Time2])
FROM #datepoints d

Salida sin procesar: Yo llamo a esto una "distancia cruda" porque el componente mensual de la distancia cardinal ''aaaa-mm'' puede ser uno demasiado; los componentes del ciclo diurno dentro del mes deben compararse para ver si este valor del último mes debe contar. Específicamente en este ejemplo, la distancia de salida sin procesar es ''12''. Pero esto es incorrecto ya que el 12/14 es antes del 12/15, por lo que solo han transcurrido 11 meses completos, es solo un día antes de que transcurra el 12 ° mes. Por lo tanto, tenemos que traer el ciclo del día intra-mes para llegar a una respuesta final. Inserte una comparación de posición de ''mes, día'' entre el para determinar si el último mes de punto de fecha cuenta nominalmente, o no:

SELECT
d.*,
Months_Between = (SELECT (SUM(l.value) - 1) FROM AZ_VBP.[MY].[edg_Linear_YearMonths] l
WHERE l.[Linear_YearMonths] BETWEEN d.[YYYY-MM of Time1] AND d.[YYYY-MM of Time2])
+ (CASE WHEN DAY(Time1) < DAY(Time2)
THEN -1
ELSE 0
END)
FROM #datepoints d

Salida final: la respuesta correcta de ''11'' es ahora nuestra salida. Y entonces, espero que esto ayude. ¡Gracias!

Answer 5

Esto es solo para ORACLE y no para SQL-Server:

months_between(to_date (''2009/05/15'', ''yyyy/mm/dd''),
to_date (''2009/04/16'', ''yyyy/mm/dd''))

Y por mes completo:

round(months_between(to_date (''2009/05/15'', ''yyyy/mm/dd''),
to_date (''2009/04/16'', ''yyyy/mm/dd'')))

Se puede usar en Oracle 8i o superior.

Answer 6

La función dateadd se puede usar para compensar al principio del mes. Si endDate tiene una parte de día menos que startDate, se aplicará al mes anterior, por lo que la fecha de vencimiento dará la cantidad correcta de meses.

datediff(month, dateadd(day,-day(startDate)+1,start),dateadd(day,-day(startDate)+1,endDate))

Answer 7

La publicación original tenía algunos errores ... así que reescribí y empaqué como una UDF.

CREATE FUNCTION FullMonthsSeparation
(
@DateA DATETIME,
@DateB DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Result INT
DECLARE @DateX DATETIME
DECLARE @DateY DATETIME
IF(@DateA < @DateB)
BEGIN
SET @DateX = @DateA
SET @DateY = @DateB
END
ELSE
BEGIN
SET @DateX = @DateB
SET @DateY = @DateA
END
SET @Result = (
SELECT
CASE
WHEN DATEPART(DAY, @DateX) > DATEPART(DAY, @DateY)
THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
ELSE DATEDIFF(MONTH, @DateX, @DateY)
END
)
RETURN @Result
END
GO
SELECT dbo.FullMonthsSeparation(''2009-04-16'', ''2009-05-15'') as MonthSep -- =0
SELECT dbo.FullMonthsSeparation(''2009-04-16'', ''2009-05-16'') as MonthSep -- =1
SELECT dbo.FullMonthsSeparation(''2009-04-16'', ''2009-06-16'') as MonthSep -- =2

Answer 8

Me doy cuenta de que esta es una publicación anterior, pero creé esta solución interesante que creo que es fácil de implementar usando una declaración CASE.

Estime la diferencia usando DATEDIFF y luego pruebe los meses anteriores y posteriores a DATEADD para encontrar la mejor fecha. Esto supone que del 31 de enero al 28 de febrero es de 1 mes (porque lo es).

DECLARE @First date = ''2015-08-31''
DECLARE @Last date = ''2016-02-28''
SELECT
@First as [First],
@Last as [Last],
DateDiff(Month, @First, @Last) as [DateDiff Thinks],
CASE
WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) +1, @First) <= @Last Then DATEDIFF(Month, @First, @Last) +1
WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) , @First) <= @Last Then DATEDIFF(Month, @First, @Last)
WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) -1, @First) <= @Last Then DATEDIFF(Month, @First, @Last) -1
END as [Actual Months Apart]

Answer 9

No es necesario crear la función solo la parte @result. Por ejemplo:

Select Name,
(SELECT CASE WHEN
DATEPART(DAY, ''2016-08-28'') > DATEPART(DAY, ''2016-09-29'')
THEN DATEDIFF(MONTH, ''2016-08-28'', ''2016-09-29'') - 1
ELSE DATEDIFF(MONTH, ''2016-08-28'', ''2016-09-29'') END) as NumberOfMonths
FROM
tableExample;

Answer 10

Tratar:

trunc(Months_Between(date2, date1))

Answer 11

SELECT 12 * (YEAR(end_date) - YEAR(start_date)) +
((MONTH(end_date) - MONTH(start_date))) +
SIGN(DAY(end_date) / DAY(start_date));

Esto funciona bien para mí en SQL Server 2000.

Answer 12

WITH
-- Count how many months must be added to @StartDate to exceed @DueDate
MONTHS_SINCE(n, [Month_hence], [IsFull], [RemainingDays] ) AS (
SELECT
1 as n,
DATEADD(Day, -1, DATEADD(Month, 1, @StartDate)) AS Month_hence
,CASE WHEN (DATEADD(Day, -1, DATEADD(Month, 1, @StartDate)) <= @LastDueDate)
THEN 1
ELSE 0
END AS [IsFull]
,DATEDIFF(day, @StartDate, @LastDueDate) as [RemainingDays]
UNION ALL
SELECT
n+1,
--DateAdd(Month, 1, Month_hence) as Month_hence -- No, causes propagation of short month discounted days
DATEADD(Day, -1, DATEADD(Month, n+1, @StartDate)) as Month_hence
,CASE WHEN (DATEADD(Day, -1, DATEADD(Month, n+1, @StartDate)) <= @LastDueDate)
THEN 1
ELSE 0
END AS [IsFull]
,DATEDIFF(day, DATEADD(Day, -1, DATEADD(Month, n, @StartDate)), @LastDueDate)
FROM MONTHS_SINCE
WHERE Month_hence<( @LastDueDate --WHERE Period= 1
)
), --SELECT * FROM MONTHS_SINCE
MONTH_TALLY (full_months_over_all_terms, months_over_all_terms, days_in_incomplete_month ) AS (
SELECT
COALESCE((SELECT MAX(n) FROM MONTHS_SINCE WHERE isFull = 1),1) as full_months_over_all_terms,
(SELECT MAX(n) FROM MONTHS_SINCE ) as months_over_all_terms,
COALESCE((SELECT [RemainingDays] FROM MONTHS_SINCE WHERE isFull = 0),0) as days_in_incomplete_month
) SELECT * FROM MONTH_TALLY;

Answer 13

select CAST(DATEDIFF(MONTH, StartDate, EndDate) AS float) -
(DATEPART(dd,StartDate) - 1.0) / DATEDIFF(DAY, StartDate, DATEADD(MONTH, 1, StartDate)) +
(DATEPART(dd,EndDate)*1.0 ) / DATEDIFF(DAY, EndDate, DATEADD(MONTH, 1, EndDate))

Answer 14

select case when DATEPART(D,End_dATE) >=DATEPART(D,sTAR_dATE)
THEN ( case when DATEPART(M,End_dATE) = DATEPART(M,sTAR_dATE) AND DATEPART(YYYY,End_dATE) = DATEPART(YYYY,sTAR_dATE)
THEN 0 ELSE DATEDIFF(M,sTAR_dATE,End_dATE)END )
ELSE DATEDIFF(M,sTAR_dATE,End_dATE)-1 END