1
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.

JJ.
  • 9,580
  • 37
  • 116
  • 189
  • 1
    There's a similar question : http://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server You can calculate how many days specified month has and put this value instead of `30` – Evaldas Buinauskas Jun 15 '15 at 17:26

2 Answers2

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.

Dannyg9090
  • 196
  • 8
  • Danny, I'm pretty much in this weird territory and that's what I need to do lol. – JJ. Jun 15 '15 at 17:38
  • Ok, so a simple example: StartDate 27/2/2014 EndDate 26/3/2014 should be 0 months? because 28 days in Feb but the datediff days is only 27. However, startdate 27/2/2014 enddate 27/3/2014 should be 1 month as the 28 days of Feb have been accounted for? Is this correct? Further to this, it needs to be able to handle multiple month boundaries - as in your example. I will update my answer in an hour or two if someone hasnt jumped in (quite busy atm). – Dannyg9090 Jun 15 '15 at 18:18
0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • example 1 doesnt work for two dates within the same month. 2014-04-01 and 2014-04-02 would resolve to 1 - I'd be surprised if he wants that. Example 2 (Adding a day) seem to be an arbitrary adjustment to make it work for the one example he quoted. example 3, is just a longhand version of example 1. – Dannyg9090 Jun 15 '15 at 18:52
  • @Dannyg9090 . . . The OP is not clear on his/her intention for different sets of dates. – Gordon Linoff Jun 17 '15 at 11:30