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
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
SQL Server 2012 has a new built-in function named EOMONTH.
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