How do I do this monthly calculation taking into account the correct number of months in SQL?

Question

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?

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.

Answer 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:

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

This achieves what my last comment lays out.

Answer 2

This answer is mostly a warning about 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.

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

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.