Geek Answers Handbook

How to get number in months between two dates in sql server 2005

I have a column in my sql server 2005 table, which should indicate the number of months during which the employee worked.

Since I also have a date when the employee was busy, 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 the column computed by the service, the results are correct several times and others incorrectly.

What would be the best reliable way to get the number of months between a DateEngaged value and the current date? What formula should I use in my calculated column?

+5
source share
4 answers

Something like (maybe you need to swap 1 and 0, unverified)

datediff(month,[DateEngaged],getdate()) +
 CASE WHEN DATEPART(day, [DateEngaged]) < DATEPART(day, getdate()) THEN 1 ELSE 0 END

DATEDIFF measures monthly boundaries, for example, 00:00, on the 1st of each month, and not the anniversary dates of the month.

Edit: after viewing the OP comment, you should subtract 1 if the start day> end of the day

DATEDIFF (month, DateEngaged, getdate()) -
 CASE
   WHEN DATEPART(day, DateEngaged) > DATEPART(day, getdate()) THEN 1 ELSE 0
 END

So, from December 20 to January 13, DATEDIFF gives 1, and then 20> 13, so subtract 1 = zero months.

+12
source

Same approach as gbn, but with less keystrokes :-)

SELECT 
    DATEDIFF(MONTH, DateEngaged, GETDATE()) +
    CASE 
        WHEN DAY(DateEngaged) < DAY(GETDATE())
        THEN 1 
        ELSE 0 
    END
+4
source

, - :

(year(getdate())-year([DateEngaged]))*12+(month(getdate())-month([DateEngaged]))
+1

, 30 , vale

round((datediff(day,[DateEngaged],getdate()))/30.00,0)
-2

More articles:


All Articles