sql server-計算SQL中兩個日期之間的完整月份數

我需要計算SQL中的完整月數,即

  • 2009-04-16至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')

但是沒有給我兩個日期之間的完整月份,而是給了我月份部分的差額,即

1

有誰知道如何計算SQL Server中的完整月份數?

asked 2020-02-23T01:21:40Z
15個解決方案
48 votes

原始帖子有一些錯誤……所以我重新編寫并將其打包為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
answered 2020-02-23T01:21:49Z
6 votes
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
answered 2020-02-23T01:22:04Z
5 votes

dateadd函數可用于偏移到月初。 如果endDate的天部分少于startDate,則它將被推到前一個月,因此datediff將給出正確的月數。

DATEDIFF(MONTH, DATEADD(DAY,-DAY(startDate)+1,startDate),DATEADD(DAY,-DAY(startDate)+1,endDate))
answered 2020-02-23T01:22:25Z
4 votes

這僅適用于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及更高版本中使用。

answered 2020-02-23T01:22:53Z
3 votes

您對一個月的定義是什么? 從技術上講,一個月可以是28、29、30或31天,具體取決于月份和leap年。

似乎您正在考慮將30個月作為一個月,因為在您的示例中您忽略了May的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
answered 2020-02-23T01:23:18Z
0 votes

DATEDIFF()用于返回指定日期范圍的兩個日期之間相交的數字邊界。 為了使它能夠執行您想要的操作,您需要進行其他調整以考慮日期何時跨越邊界但沒有完成整個跨度。

answered 2020-02-23T01:23:38Z
0 votes
SELECT 12 * (YEAR(end_date) - YEAR(start_date)) +
    ((MONTH(end_date) - MONTH(start_date))) +
    SIGN(DAY(end_date) / DAY(start_date));

這對我在SQL SERVER 2000上工作正常。

answered 2020-02-23T01:23:58Z
0 votes

嘗試:

trunc(Months_Between(date2, date1))
answered 2020-02-23T01:24:18Z
0 votes
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;   
answered 2020-02-23T01:24:33Z
0 votes

只需創建@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;
answered 2020-02-23T01:24:53Z
0 votes

該答案遵循T-SQL格式。 我將這個問題概念化為日期時間格式中兩個日期點之間的線性時間距離之一,將它們稱為Time1和Time2。 Time1應該與您要處理的“較早的時間”值(例如出生日期或小部件創建日期或旅程的開始日期)對齊,而Time2應該與“較新的時間”值(例如快照日期)對齊 或小部件完成日期或到達旅程檢查點的日期)。

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

該解決方案利用簡單的測量,轉換和計算不同長度的多個循環的串行交點的能力。 此處:世紀,十年,年,月,日(感謝瑪雅日歷的概念!)。 快速感謝:感謝Stack Overflow的其他貢獻者,向我展示了我在此過程中縫合的一些組件功能。 我在這個論壇上對我的評價很高。

首先可以领救济金的游戏,構造一個地平線,該地平線是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”是我們的輸出。 因此,我希望這會有所幫助。 謝謝!

answered 2020-02-23T01:25:44Z
0 votes
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))
answered 2020-02-23T01:25:59Z
0 votes

我意識到這是一篇老文章,但是我創建了這個有趣的解決方案,我認為使用CASE語句很容易實現。

使用DATEDIFF估算差值,然后使用DATEADD測試前后的月份以找到最佳日期。 假設1月31日至2月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]
answered 2020-02-23T01:26:24Z
0 votes

簡單易行,只需將此完整代碼復制并粘貼到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
answered 2020-02-23T01:26:44Z
-2 votes

我通過互聯網搜索。我發現的建議是在末尾加上+1。

嘗試這樣做:

Declare @Start DateTime
Declare @End DateTime
Set @Start = '11/1/07'
Set @End = '2/29/08'
Select DateDiff(Month, @Start, @End + 1)
answered 2020-02-23T01:27:08Z
translate from