如何获取sql server 2005中两个日期之间的月份数

I have a column in my sql server 2005 table that should hold the number of months an employee has been in service.

我的sql server 2005表中有一列应该保存员工服务的月数。

Since I also have the date the employee was engaged, I want the "months_In_Service" column to be a computed column.

由于我也有雇员参与的日期,我希望“months_In_Service”列成为计算列。

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.

现在,如果我使用DATEDIFF(月,[DateEngaged],GETDATE())作为服务计算列中月份的公式,则结果有时是正确的,有时则不正确。

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?

获得DateEngaged值和当前日期之间的月数的更可靠的方法是什么?我应该在计算列中使用哪个公式?

4 个解决方案

#1

Something like (might need to swap the 1 and 0, untested)

类似的东西(可能需要交换1和0,未经测试)

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

DATEDIFF测量月份边界,例如每月1日00:00时间,而不是日期周年纪念日

Edit: after seeing OP's comment, you have to subtract 1 if the start day > end day

编辑:看到OP的评论后,如果开始日期>结束日,则必须减去1

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.

因此,对于12月20日至1月13日,DATEDIFF给出1然后20> 13因此减去1 =零月。

#2

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

与gbn相同,但键击次数较少:-)

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

#3

Maybe you want something like:

也许你想要的东西:

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

#4

-2

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

如果你认为这个月意味着30天你也可以圆形

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

#1