how to get the number on months between two dates in sql server 2005

Question

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?

Could you provide some examples of what you mean when you say that sometimes they are correct and sometimes they are not. — My Other Me, Jan 13 '10 at 07:04
Could you please retag this (edit) and set sql-server-2005 (or even just sql-server)? Note the dashes. There are users here who ignore SQL Server questions (not their expertise) but are interested in SQL questions. — Jürgen A. Erhard, Jan 13 '10 at 07:21
DateEngaged = '12/20/2009" CurrentDate = '1/13/2010" That gives me months in service as 1 using formular "datediff(month,[DateEngaged],getdate()+(1))" It also gives years in service as 1 using formula "datediff(year,[DateEngaged],getdate()+(1))" — StackTrace, Jan 13 '10 at 07:25

gbn · Accepted Answer · 2010-01-13T07:30:21.910

12

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.

edited Jan 13 '10 at 07:30

answered Jan 13 '10 at 07:12

gbn

422,506
82
585
676

this seems to work correctly, am just going to test it more just in case. – StackTrace Jan 13 '10 at 07:44
1

Did you ever test what happens when the employee engaged on 31 October and the current date was 30 November? This routine returns 0 months, but in fact, virtually every business requirement I can imagine would say that is a month. Found this looking for a solution to that very problem. Still looking...... – Ron Mar 28 '16 at 18:21
Not a brilliant solution to what @Ron is explaining? – toscanelli Aug 23 '17 at 10:57

score 4 · Answer 2 · answered Jan 13 '10 at 07:23

4

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

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

answered Jan 13 '10 at 07:23

Frank Kalis

1,322
9
8

score 1 · Answer 3 · answered Jan 13 '10 at 07:13

1

Maybe you want something like:

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

answered Jan 13 '10 at 07:13

My Other Me

5,007
6
41
48

score -2 · Answer 4 · answered Jan 13 '10 at 07:20

-2

If You presume that month is meaning for 30 days You can also round vale

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

answered Jan 13 '10 at 07:20

adopilot

4,340
12
65
92

i exactly want month to mean 30 days and year twelve months of 30 days (each!!). – StackTrace Jan 13 '10 at 07:30
5

This drifts further and further out as time goes on. After 6 years, the error is a whole month, guaranteed – gbn Jan 13 '10 at 07:31

how to get the number on months between two dates in sql server 2005

4 Answers4

Linked