Вычисление количества полных месяцев между двумя датами в SQL

Мне нужно рассчитать число FULL месяц в SQL, т.е.

2009-04-16 to 2009-05-15 = > 0 полный месяц
2009-04-16 по 2009-05-16 = > 1 полный месяц
2009-04-16 по 2009-06-16 = > 2 полных месяца

Я попытался использовать DATEDIFF, т.е.

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

но вместо того, чтобы дать мне полные месяцы между двумя датами, это дает мне разницу в части месяца, т.е.

Кто-нибудь знает, как вычислить количество полных месяцев в SQL Server?

Ответ 1

В исходном посте были некоторые ошибки... поэтому я переписал и упаковал его как 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

Ответ 2

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

Ответ 3

Функция dateadd может быть использована для смещения до начала месяца. Если у endDate часть дня меньше, чем startDate, он будет перенесен на предыдущий месяц, поэтому datediff даст правильное количество месяцев.

DATEDIFF(MONTH, DATEADD(DAY,-DAY(startDate)+1,startDate),DATEADD(DAY,-DAY(startDate)+1,endDate))

Ответ 4

Это только для ORACLE, а не для SQL-Server:

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

И за полный месяц:

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

Может использоваться в Oracle 8i и выше.

Ответ 5

Какое ваше определение месяца? Технически месяц может составлять 28,29,30 или 31 день в зависимости от месяца и високосного года.

Кажется, вы рассматриваете месяц на 30 дней, так как в вашем примере вы проигнорировали, что май имеет 31 день, так почему бы просто не сделать следующее?

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

Ответ 6

DATEDIFF() предназначен для возврата числовых границ, пересекающихся между двумя датами для указанного диапазона. Чтобы заставить его делать то, что вы хотите, вам нужно внести дополнительную корректировку в учетную запись, когда даты пересекают границу, но не заполняют полный интервал.

Ответ 7

Попробуйте:

trunc(Months_Between(date2, date1))

Ответ 8

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

Это отлично работает для меня в SQL SERVER 2000.

Ответ 9

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;

Ответ 10

Не нужно создавать функцию только для части @result. Например:

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;

Ответ 11

Этот ответ следует за форматом T-SQL. Я рассматриваю эту проблему как одно из линейного расстояния между двумя точками даты в формате даты и времени, вызывая их Time1 и Time2; Time1 должен быть привязан к значению "старше во времени", которое вы имеете в виду (например, дата рождения или дата создания виджета или дата начала поездки), а Time2 следует выровнять со значением "более новое время" (например, дата моментального снимка или датой завершения виджета или датой завершения поездки).

DECLARE @Time1 DATETIME
SET @Time1 = '12/14/2015'

DECLARE @Time2 DATETIME
SET @Time2 = '12/15/2016'

Решение использует простые измерения, преобразования и вычисления последовательных пересечений нескольких циклов разной длины; здесь: Century, Decade, Year, Month, Day (Спасибо, майянский календарь за концепцию!). Быстрая благодарность: я благодарю других участников Qaru за то, что они показали мне некоторые функции компонента в этом процессе, которые я сшил вместе. Я положительно оценил их в свое время на этом форуме.

Сначала постройте горизонт, который представляет собой линейный набор пересечений циклов Century, Decade, Year, Month, поэтапно по месяцам. Используйте для этого крестовую декартову функцию. (Подумайте об этом как о создании ткани, из которой мы будем вырезать длину между двумя точками "yyyy-mm" для измерения расстояния):

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

Затем преобразуйте точки времени Time1 и Time2 в формат "yyyy-mm" (подумайте об этом как о точках пересечения координат на всей ткани). Сохраните исходные версии точек времени и времени:

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

Затем выберите порядковое расстояние единиц "yyyy-mm", меньше одного, чтобы перейти на расстояние до кардинала (например, вырезать кусок ткани из всей ткани в обозначенных точках вырезания и получить свое сырое измерение):

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

Необработанный выход: Я называю это "необработанным расстоянием", потому что месячная составляющая кардинального расстояния "yyyy-mm" может быть слишком большой; компоненты дневного цикла в течение месяца необходимо сравнить, чтобы увидеть, должно ли это значение в прошлом месяце рассчитываться. В этом примере, в частности, исходное расстояние вывода равно "12". Но это не так, как 12/14, до 12/15, так что прошло всего 11 полных месяцев - его просто один день застенчивый, истекающий через 12-й месяц. Поэтому мы должны ввести внутримесячный дневной цикл, чтобы получить окончательный ответ. Вставьте сравнение позиции "месяц, день", чтобы определить, является ли последний месяц точки даты номинальным, или нет:

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

Конечный результат: Правильный ответ "11" теперь является нашим выходом. И поэтому я надеюсь, что это поможет. Спасибо!

Ответ 12

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))

Ответ 13

Я понимаю, что это старый пост, но я создал это интересное решение, которое, я думаю, легко реализовать с помощью оператора CASE.

Оцените разницу с помощью DATEDIFF, а затем протестируйте месяцы до и после использования DATEADD, чтобы найти наилучшую дату. Это предполагает, что 31 января - 28 февраля - 1 месяц (потому что это так).

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]

Ответ 14

ПРОСТОЙ И ЛЕГКИЙ СПОСОБ, просто скопируйте и вставьте этот ПОЛНЫЙ код в MS SQL и выполните:

declare @StartDate date='2019-01-31'
declare @EndDate date='2019-02-28'


SELECT

DATEDIFF(MONTH, @StartDate, @EndDate)+

(

case 

when format(@StartDate,'yyyy-MM') != format(@EndDate,'yyyy-MM') AND DATEPART(DAY,@StartDate) > DATEPART(DAY,@EndDate) AND DATEPART(DAY,@EndDate) = DATEPART(DAY,EOMONTH(@EndDate)) then 0

when format(@StartDate,'yyyy-MM') != format(@EndDate,'yyyy-MM') AND DATEPART(DAY,@StartDate) > DATEPART(DAY,@EndDate)  then -1 

else 0 

end

) 

as NumberOfMonths

Ответ 15

Я googled через Интернет. И предложение, которое я нашел, - добавить +1 к концу.

Попробуйте сделать это следующим образом:

Declare @Start DateTime
Declare @End DateTime

Set @Start = '11/1/07'
Set @End = '2/29/08'

Select DateDiff(Month, @Start, @End + 1)