i have a column in my sql server 2005 table that should hold the number of months an employee has been in service. Since i also have the date the employee was engaged, i want the "months_In_Service" column to be a computed column. now if i use datediff(month,[DateEngaged],getdate()) as the formula for the months in service computed column, the results are correct some times and other times incorrect. what would be the better reliable way to get the number of months between the DateEngaged value and the current date. which formula should i use in my computed column.
+4
A:
Something like (might need to swap the 1 and 0, untested)
datediff(month,[DateEngaged],getdate()) +
CASE WHEN DATEPART(day, [DateEngaged]) < DATEPART(day, getdate()) THEN 1 ELSE 0 END
DATEDIFF measure month boundaries eg 00:00 time on 1st of each month, not day-of-month anniversaries
Edit: after seeing OP's comment, you have to subtract 1 if the start day > end day
DATEDIFF (month, DateEngaged, getdate()) -
CASE
WHEN DATEPART(day, DateEngaged) > DATEPART(day, getdate()) THEN 1 ELSE 0
END
So for 20 Dec to 13 Jan, DATEDIFF gives 1 and then 20 > 13 so subtract 1 = zero months.
gbn
2010-01-13 07:12:52
this seems to work correctly, am just going to test it more just in case.
Name.IsNullOrEmpty
2010-01-13 07:44:54
A:
Maybe you want something like:
(year(getdate())-year([DateEngaged]))*12+(month(getdate())-month([DateEngaged]))
My Other Me
2010-01-13 07:13:37
A:
If You presume that month is meaning for 30 days You can also round vale
round((datediff(day,[DateEngaged],getdate()))/30.00,0)
adopilot
2010-01-13 07:20:16
i exactly want month to mean 30 days and year twelve months of 30 days (each!!).
Name.IsNullOrEmpty
2010-01-13 07:30:54
This drifts further and further out as time goes on. After 6 years, the error is a whole month, guaranteed
gbn
2010-01-13 07:31:47
A:
Same approach as gbn, but with less keystrokes :-)
SELECT
DATEDIFF(MONTH, DateEngaged, GETDATE()) +
CASE
WHEN DAY(DateEngaged) < DAY(GETDATE())
THEN 1
ELSE 0
END
Frank Kalis
2010-01-13 07:23:59