SQL 计算SQL中两个日期之间的完整月数

Question

提问by oscarkuo

I need to calculate the number of FULLmonth in SQL, i.e.

我需要在 SQL 中计算FULL月份的数量，即

2009-04-16 to 2009-05-15 => 0 full month
2009-04-16 to 2009-05-16 => 1 full month
2009-04-16 to 2009-06-16 => 2 full months

2009-04-16 到 2009-05-15 => 0 整月
2009-04-16 到 2009-05-16 => 1 整月
2009-04-16 到 2009-06-16 => 2 整月

I tried to use DATEDIFF, i.e.

我尝试使用 DATEDIFF，即

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

but instead of giving me full months between the two date, it gives me the difference of the month part, i.e.

但不是给我两个日期之间的完整月份，而是给我月份部分的差异，即

anyone know how to calculate the number of full months in SQL Server?

有谁知道如何计算 SQL Server 中的完整月数？

Answer 1

采纳答案by Joe Davis

The original post had some bugs... so I re-wrote and packaged it as a UDF.

原来的帖子有一些错误……所以我重新编写并打包为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 2

回答by Shankar

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

Answer 3

回答by MyItchyChin

What's your definition of a month? Technically a month can be 28,29,30 or 31 days depending on the month and leap years.

你对一个月的定义是什么？从技术上讲，一个月可以是 28、29、30 或 31 天，具体取决于月份和闰年。

It seems you're considering a month to be 30 days since in your example you disregarded that May has 31 days, so why not just do the following?

似乎您正在考虑将一个月设为 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

Answer 4

回答by Jason Brady

The dateadd function can be used to offset to the beginning of the month. If the endDate has a day part less than startDate, it will get pushed to the previous month, thus datediff will give the correct number of months.

dateadd 函数可用于偏移到月初。如果 endDate 的一天部分小于 startDate，它将被推送到上个月，因此 datediff 将给出正确的月数。

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

Answer 5

回答by Stephan Schielke

This is for ORACLEonly and not for SQL-Server:

这仅适用于ORACLE，不适用于 SQL-Server：

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

And for full month:

整月：

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

Can be used in Oracle 8i and above.

可以在Oracle 8i 及以上版本中使用。

Answer 6

回答by Adrian

Is not necesary to create the function only the @result part. For example:

不需要只创建@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;

Answer 7

回答by E.Gannon

This answer follows T-SQL format. I conceptualize this problem as one of a linear-time distance between two date points in datetimeformat, call them Time1 and Time2; Time1 should be aligned to the 'older in time' value you are dealing with (say a Birth date or a widget Creation date or a journey Start date) and Time2 should be aligned with the 'newer in time' value (say a snapshot date or a widget completion date or a journey checkpoint-reached date).

此答案遵循 T-SQL 格式。我概念化这个问题在两个日期点之间的线性时间距离中的一个日期时间的格式，称它们为时间1和时间2; Time1 应与您正在处理的“较旧的时间”值（例如出生日期或小部件创建日期或旅程开始日期）对齐，而 Time2 应与“较新的时间”值（例如快照日期）对齐或小部件完成日期或旅程检查点到达日期）。

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

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

The solution leverages simple measurement, conversion and calculations of the serial intersections of multiple cycles of different lengths; here: Century,Decade,Year,Month,Day (Thanks Mayan Calendar for the concept!). A quick note of thanks: I thank other contributors to Stack Overflow for showing me some of the component functions in this process that I've stitched together. I've positively rated these in my time on this forum.

该解决方案利用了对不同长度的多个周期的串行交叉点的简单测量、转换和计算；此处：世纪、十年、年、月、日（感谢玛雅日历的概念！）。感谢的简短说明：我感谢 Stack Overflow 的其他贡献者向我展示了我在此过程中拼接在一起的一些组件功能。我在这个论坛上对这些进行了积极评价。

First, construct a horizon that is the linear set of the intersections of the Century,Decade,Year,Month cycles, incremental by month. Use the cross join Cartesian function for this. (Think of this as creating the cloth from which we will cut a length between two 'yyyy-mm' points in order to measure distance):

首先，构建一个地平线，它是世纪、十年、年、月周期的交集的线性集合，逐月递增。为此使用交叉连接笛卡尔函数。（将此视为创建布料，我们将在布料上切割两个“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

Then, convert your Time1 and Time2 date points into the 'yyyy-mm' format (Think of these as the coordinate cut points on the whole cloth). Retain the original datetimeversions of the points as well:

然后，将您的 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

Then, Select the ordinal distance of 'yyyy-mm' units, less one to convert to cardinal distance (i.e. cut a piece of cloth from the whole cloth at the identified cut points and get its raw measurement):

然后，选择 '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

Raw Output:I call this a 'raw distance' because the month component of the 'yyyy-mm' cardinal distance may be one too many; the day cycle components within the month need to be compared to see if this last month value should count. In this example specifically, the raw output distance is '12'. But this wrong as 12/14 is before 12/15, so therefore only 11 full months have lapsed--its just one day shy of lapsing through the 12th month. We therefore have to bring in the intra-month day cycle to get to a final answer. Insert a 'month,day' position comparison between the to determine if the latest date point month counts nominally, or not:

原始输出：我将其称为“原始距离”，因为“yyyy-mm”基数距离的月份分量可能过多；需要比较该月内的日周期组件，以查看是否应计算上个月的值。在这个例子中，原始输出距离是“12”。但是这个错误，因为 12/14 是在 12/15 之前，因此只过去了整整 11 个月——距离第 12 个月只差一天了。因此，我们必须引入月内日周期才能得出最终答案。在之间插入 'month,day' 位置比较以确定最新的日期点月份是否名义上计数：

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

Final Output:The correct answer of '11' is now our output. And so, I hope this helps. Thanks!

最终输出：'11' 的正确答案现在是我们的输出。所以，我希望这会有所帮助。谢谢！

Answer 8

回答by Ren Yuzhi

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 9

回答by kevro

I realize this is an old post, but I created this interesting solution that I think is easy to implement using a CASE statement.

我意识到这是一篇旧帖子，但我创建了这个有趣的解决方案，我认为使用 CASE 语句很容易实现。

Estimate the difference using DATEDIFF, and then test the months before and after using DATEADD to find the best date. This assumes Jan 31 to Feb 28 is 1 month (because it is).

使用 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]

Answer 10

回答by Shehan Silva

SIMPLE AND EASY WAY, Just Copy and Paste this FULL code to MS SQL and Execute :

简单而简单的方法，只需将此完整代码复制并粘贴到 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

SQL 计算SQL中两个日期之间的完整月数

提问by oscarkuo

采纳答案by Joe Davis

回答by Shankar

回答by MyItchyChin

回答by Jason Brady

回答by Stephan Schielke

回答by Adrian

回答by E.Gannon

回答by Ren Yuzhi

回答by kevro

回答by Shehan Silva

相关推荐

最近更新

标签

SQL 计算SQL中两个日期之间的完整月数

提问by oscarkuo

采纳答案by Joe Davis

回答by Shankar

回答by MyItchyChin

回答by Jason Brady

回答by Stephan Schielke

回答by Adrian

回答by E.Gannon

回答by Ren Yuzhi

回答by kevro

回答by Shehan Silva

相关推荐

SQL 与 WHERE 子句一起使用时优化 Oracle CONNECT BY

SQL 具有多列和 Desc/Asc 排序的 Order By 子句的 CASE 语句

了解涉及 3 个或更多表时 JOIN 的工作原理。[SQL]

SQL 如何在netezza中将字符串转换为数字

相关推荐

最近更新

标签