sql server中两个日期之间的月差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20677122/
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
Month difference between two dates in sql server
提问by Jeswanth
Please refer the below examples and kindly let me know your ideas.
请参考以下示例，并请让我知道您的想法。
declare @EmployeeStartDate datetime='01-Sep-2013'
declare @EmployeeEndDate datetime='15-Nov-2013'
select DateDiff(mm,@EmployeeStartDate, DateAdd(mm, 1,@EmployeeEndDate))
Output = 3expected output = 2.5
输出 =3预期输出 =2.5
Since I have only 15 days in Nov, So I should get 0.5for Nov
由于我在 11 月只有 15 天，所以我应该去0.511 月
回答by Vignesh Kumar A
Try this
尝试这个
SELECT CASE WHEN DATEDIFF(d,'2013-09-01', '2013-11-15')>30 THEN DATEDIFF(d,'2013-09-01', '2013-11-15')/30.0 ELSE 0 END AS 'MonthDifference'
OR
或者
SELECT DATEDIFF(DAY, '2013-09-01', '2013-11-15') / 30.436875E
回答by Ren Yuzhi
select CAST(DATEDIFF(MONTH, StartDate, EndDate) AS float) -
(DATEPART(dd,StartDate)*1.0 - 1.0) / DAY(EOMONTH(StartDate)) +
((DATEPART(dd,EndDate)*1.0 ) / DAY(EOMONTH(EndDate)))
回答by Obsidian Phoenix
DateDiff compares the values of the column you specify to work out the difference, it doesn't compare both dates and give you an exact difference. You've told it to compare the Month values, so thats all it's looking it.
DateDiff 比较您指定的列的值以计算出差异，它不会比较两个日期并为您提供确切的差异。您已经告诉它比较 Month 值，这就是它的全部内容。
http://technet.microsoft.com/en-us/library/ms189794.aspx
http://technet.microsoft.com/en-us/library/ms189794.aspx
The Technet article details the return value of the DateDiff Function - note that it's only int.
Technet 文章详细介绍了 DateDiff 函数的返回值 - 请注意它只是 int。
If you want the value as an exact figure (or nearabouts), you should datediff the dates on days, then divide by 30. For neatness, I've also rounded to a single decimal place.
如果您希望该值是一个精确数字（或近似值），您应该对日期进行日期区分，然后除以 30。为了简洁起见，我还四舍五入到一个小数位。
select Round(Convert(decimal, DateDiff(dd,@EmployeeStartDate, @EmployeeEndDate)) / 30, 1)
回答by Vishal Suthar
Here you go:
干得好：
declare @EmployeeStartDate datetime='01-Sep-2013'
declare @EmployeeEndDate datetime='15-Nov-2013'
;WITH cDayDiff AS
(
select DateDiff(dd,@EmployeeStartDate, DateAdd(dd, 1,@EmployeeEndDate)) as days
)
SELECT
CAST(days as float) / 30 as Months
FROM
cDayDiff
It has 76days which equals to 2.5333
它的76天数等于2.5333
Output:
输出：
Months
============
2.53333333333333
回答by pius
As far as I can tell, none of the other answers take into account that not all months are exactly 30 days long.
据我所知，其他答案都没有考虑到并非所有月份都是 30 天。
This is what I came up with:
这就是我想出的：
DECLARE @StartDate DATETIME = '07-Oct-2018'
DECLARE @EndDate DATETIME = '06-Nov-2018'
SELECT
DATEDIFF(m, @StartDate, @EndDate)
+ 1.0 * DAY(@EndDate) / DAY(EOMONTH(@EndDate))
- 1.0 * DAY(@StartDate) / DAY(EOMONTH(@StartDate))
-- 0.974193548388
The formula can be explained like this:
公式可以这样解释：
- The difference in months as output by
DATEDIFF(m, ..., ...) - Plus the day of the last date divided by the number of days in that month
- Minus the day of the first date divided by the number of days in that month
- 作为输出的月数差异为
DATEDIFF(m, ..., ...) - 加上最后一个日期的天数除以该月的天数
- 减去第一个日期的天数除以该月的天数
Note that in this case the answer from Ren Yuzhi gives the result 1.006451612904. The 1.0is necessary to make the division happen in floating point rather than integer.
请注意，在这种情况下，任宇智的答案给出了结果 1.006451612904。在1.0有必要使浮点，而不是整数除法发生。
回答by chandra shekar reddy dubba
You can use Below to calculate the No Of Months between two Dates in MySQL,
您可以使用下面来计算 MySQL 中两个日期之间的月数，
PERIOD_DIFF(concat(year(Date1),LPAD(month(Date1),2,0)),concat(year(Date2),LPAD(month(Date2),2,0)))
回答by user1308314
declare @EmployeeStartDate datetime='013-09-01'
declare @EmployeeEndDate datetime='2013-11-15'
SELECT DATEDIFF(month, @EmployeeStartDate, @EmployeeEndDate)
Thanks for this. https://www.w3schools.com/sql/func_sqlserver_datediff.asp
谢谢你。 https://www.w3schools.com/sql/func_sqlserver_datediff.asp
