SQLTeam.com | Weblogs | Forums

Date Difference Include end date in calculation


#1

Hi,

How to get Month Difference between 2 date, when should add 1 or not? The last command return 11 instead of 12.

I need:
select DateDiff(month, dbo.ufn_GetFirstDayOfMonth('2012-04-01'),dbo.udf_GetLastDayOfMonth('2012-10-30')) --6 correct
select DateDiff(month, dbo.ufn_GetFirstDayOfMonth('2012-04-01'), dbo.udf_GetLastDayOfMonth('2013-01-30')) --9 correct
select DateDiff(month, dbo.ufn_GetFirstDayOfMonth('2012-04-01'), dbo.udf_GetLastDayOfMonth('2013-03-31')) --11 But i need it to return as 12

Please advise.

Thank you.

Regards,
Micheale


#2

I manage to solved it with function:-

Depreciation_Month_End=dbo.GetMonthEnd(DateAdd(m,Depreciation_Month,Date_Purchased)
select DateDiff(month, dbo.ufn_GetFirstDayOfMonth('2012-04-01'), dbo.udf_GetLastDayOfMonth(Depreciation_Month_End))

CREATE Function GetMonthEnd(@Date DateTime)
Returns DateTime
AS
Begin
Return (dateadd(mm, datediff(mm, 0, @Date) + 1, 0) - 1)
End


#3

SQL Server 2012 has a new built-in function named EOMONTH.


#4

Beware that those User Defined (Scalar) Functions tend to have dreadful performance - that will be particularly important if you execute them in the SELECT for every row of a large resultset or, worse, in the WHERE clause.

Personally I would just in-line that "dateadd(mm, datediff(mm, 0, @Date) + 1, 0) - 1" code instead of the UDF, wherever you need it (but try to avoid using in a WHERE clause as it will make the query non-SARGable). Although awkward to read it has the benefit that everything is an integer arithmetic process, so it is very fast (no data type conversions required etc.)

Also, for me, I would replace "mm" with "Month". It reads better and there is no confusion between "mm" and Month, Minute, Millisecond, Microsecond; my view is that those abbreviated mnemonics are at serious risk of introducing bugs during software maintenance