考虑到SQL中正确的月数，我该如何进行月度计算？

DECLARE @InputPeriodStart DATE = '1/1/2014'
DECLARE @InputPeriodEnd DATE = '12/31/2014'

ROUND(CONVERT(DECIMAL, DATEDIFF(dd, @InputPeriodStart, @InputPeriodEnd)) / 30, 1) AS DECIMAL(18, 2))

The issue here is that not every month has 30 days in it. So how can I make this calculation work properly?

这里的问题是,并非每个月都有30天。那么我怎样才能使这个计算正常工作呢?

I would need to remove the ROUND() and then replace the 30 with the actual number of days for each month. I'm not sure how I'd do that.

我需要删除ROUND(),然后将30替换为每个月的实际天数。我不确定我是怎么做到的。

2 个解决方案

#1

Is this what you're looking for?

这是你在找什么?

DATEDIFF(mm, @InputPeriodStart, @InputPeriodEnd))

If you are trying to do something a bit weirder like adjust for the days in the month your "periodstart" is in - then you are getting into some weird territory but it is still possible. Just drop a comment to specify.

如果你想做一些奇怪的事情,比如调整你的“期间启动”所在月份的日子 - 那么你会进入一些奇怪的领域,但它仍然是可能的。只需删除注释即可指定。

Edit:

take a look at this SQLFiddle:

看看这个SQLFiddle:

http://sqlfiddle.com/#!6/a1463/7

This achieves what my last comment lays out.

这实现了我上次评论的内容。

#2

This answer is mostly a warning about datediff().

这个答案主要是关于datediff()的警告。

For your example, datediff(day, @InputPeriodStart, @InputPeriodEnd) returns 11 and not 12. That is because datediff() counts the number of month boundaries between the two values.

对于您的示例,datediff(day,@ InputPeriodStart,@ InputPeriodEnd)返回11而不是12.这是因为datediff()计算两个值之间的月边界数。

I am guessing that you want 12. Two ways you can get this:

我猜你想要12.你有两种方法可以得到这个:

DATEDIFF(month, @InputPeriodStart, @InputPeriodEnd)) + 1

or:

DATEDIFF(month, @InputPeriodStart, DATEADD(day, 1, @InputPeriodEnd))

Or, if you don't care about the specific day, you can also do arithmetic on the year and month values:

或者,如果您不关心特定日期,您还可以对年和月值进行算术运算:

(1 + (YEAR(@InputPeriodEnd) * 12 + MONTH(@InputPeriodEnd)) -
 (YEAR(@InputPeriodEnd) * 12 + MONTH(@InputPeriodStart))
)

The semantics of DATEDIFF() can be a bit confusing. The definition is fine (in general) for seconds and when using day for dates. At other times, it can lead to off-by-one errors that are hard to detect and solve.

DATEDIFF()的语义可能有点令人困惑。定义很好(一般情况下)几秒钟和日期使用日期。在其他时候,它可能导致难以检测和解决的逐个错误。

#1