不返回正确值之间的月份 [英] Months between not returning correct value

问题描述

Select MOD (RUNC (MONTHS_BETWEEN (TO_DATE ('28-Mar-2017', 'DD-MON-YYYY'),
 TO_DATE ('28-Feb-2017', 'DD-MON-YYYY'))), 12) Months from dual

为什么这个函数即使没有完成一个月也要返回1个月?它应该是0

Why is this function returning 1 month even if not completed the month? Its supposed to be 0

推荐答案

您错了. MONTHS_BETWEEN将从date '2017-02-28'到date '2017-03-28'的时间跨度精确地视为一个月(相邻两个月中的同一天).可以在文档中阅读: https://docs.oracle .com/cd/B19306_01/server.102/b14200/functions089.htm

You are wrong. MONTHS_BETWEEN treats the time span from date '2017-02-28' to date '2017-03-28' as exactly one month (same day in two adjacent months). This can be read in the docs: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions089.htm

MONTHS_BETWEEN返回日期date1和date2之间的月数.如果date1晚于date2，则结果为正.如果date1早于date2，则结果为负数.如果date1和date2是一个月的同一天或两个月的最后几天，则结果始终是整数.否则，Oracle数据库将基于31天的月份来计算结果的小数部分，并考虑时间分量date1和date2的差异.

MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

一个日期大于另一个日期，因此您将获得一个正数或负数(即不为零)，具体取决于第一个参数和第二个参数，并且当天数相等时，结果将为整数.相邻月份为1或-1.

One date is bigger than the other so you get a positive or negative number (i.e. not zero) depending on which is first and which is second parameter, and as the days equal, the result will be an integer. That is 1 or -1 for adjacent months.

月计算无论如何都是一件奇怪的事，因为一个月不是一个定义的时间跨度.您似乎在脑海中有一个确定的定义，这与MONTHS_BETWEEN的定义完全不同.没错.在这种情况下，我同意MONTHS_BETWEEN.从2月28日到3月28日正好是一个月.如果您需要不同的规则，请应用自己的数学方法:-)

Month calculation is a strange thing anyway, as a month is not a defined time span. You seem to have a certain definition in mind, which simply is different from how MONTHS_BETWEEN defines it. Nothing wrong about that. I would agree with MONTHS_BETWEEN in this case; from Feb 28 to Mar 28 is "exactly" one month. If you want different rules, then apply your own math :-)

这篇关于不返回正确值之间的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！