在sql server 2005中如何获取两个日期之间的月份数 [英] how to get the number on months between two dates in sql server 2005

问题描述

我的sql server 2005表中有一列，该列应该保留员工已经在使用的月数。既然我也有雇员聘用的日期，我希望months_In_Service列成为计算列。现在如果我使用datediff（month，[DateEngaged]，getdate（））作为服务计算列中的月份的公式，结果是正确的，有些时候是不正确的。获取DateEngaged值和当前日期之间的月份的更好的可靠方法是什么？我应该在我的计算列中使用哪个公式。

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.

推荐答案

有些东西（可能需要交换1和0，未经测试）

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测量月份边界，例如00:00每月1日的时间，不是月的周年纪念日

DATEDIFF measure month boundaries eg 00:00 time on 1st of each month, not day-of-month anniversaries

编辑：看到OP的评论后，如果开始日>结束日，你必须减1 p>

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

所以12月20日到1月13日，DATEDIFF给1，然后20> 13，所以减1 =零个月。

So for 20 Dec to 13 Jan, DATEDIFF gives 1 and then 20 > 13 so subtract 1 = zero months.

这篇关于在sql server 2005中如何获取两个日期之间的月份数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！