Nickson Asiimwe

Pardon me for injecting this comment, but I believe your problem to some extent currently lies outside of being a T-SQL problem. Rather it lies in the realm of business rules. For example:

Employee 1 starts work on Dec 1 2009 -- a tuesday

Employee 2 starts work on Dec 22 2009 -- a tuesday

Using the DATEDIFF(Month .... function, both would show a working period of one (1) month if the date of the calculation was a day in January 2010.

Now if I were Employee 1 that would be correct, but if some benefit were based on number of months worked, I would be upset that Employee 2 would receive the same benefit (say a pay raise or a vacation day, whatever) even though employee 2 worked less days that I did.

Do your current business rules say that this is what is desired, or do your business rules state that if the day of the calculation (Jan 21) is less than the day of engagement (Dec 22) then the employee should not be given credit for a month of work? Of course the business rules will make the calculation slightly more complex, but that is the price that must be paid to be accurate.

Articulate those rules and post as a NEW forum question and I am sure someone will attempt to assist you.