Menu
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?
571
The MONTHS_BETWEEN() function is used to get the number of months between dates (date1, date2). See the following conditions: If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative.
The MONTH() function returns the month part for a specified date (a number from 1 to 12).
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
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.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
