SQL 如何在sql server 2005中获取两个日期之间的月份数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2054975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get the number on months between two dates in sql server 2005
提问by StackTrace
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(month,[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 值和当前日期之间的月数的更可靠的方法是什么？我应该在计算列中使用哪个公式？
回答by gbn
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 = 零个月。
回答by Frank Kalis
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
回答by My Other Me
Maybe you want something like:
也许你想要这样的东西：
(year(getdate())-year([DateEngaged]))*12+(month(getdate())-month([DateEngaged]))
回答by adopilot
If You presume that month is meaning for 30 days You can also round vale
如果您认为该月有 30 天的意义，您也可以将 vale 取整
round((datediff(day,[DateEngaged],getdate()))/30.00,0)
