SQL ADD_MONTHS 函数在 Oracle 中没有返回正确的日期

Question

提问by Mohamed Saligh

See the results of below queries:

查看以下查询的结果：

>> SELECT ADD_MONTHS(TO_DATE('30-MAR-11','DD-MON-RR'),-4) FROM DUAL;
30-NOV-10


>> SELECT ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR'),4) FROM DUAL;
31-MAR-11

How can I get '30-MAR-11' when adding 4 months to some date?

将 4 个月添加到某个日期时如何获得“30-MAR-11”？

Please help.

请帮忙。

Answer 1

采纳答案by M'vy

There is another question here about Oracle and Java

这里还有一个关于Oracle 和 Java 的问题

It states that

它指出

From the Oracle reference on add_months http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions004.htm
If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

来自 add_months 上的 Oracle 参考http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions004.htm
如果 date 是该月的最后一天，或者如果结果月份的天数少于 date 的天数，则结果是结果月份的最后一天。否则，结果与日期具有相同的日期部分。

So I guess you have to manually check stating day and ending day to change the behaviour of the function. Or maybe by adding days instead of months. (But I didn't find a add_dayfunction in the ref)

所以我猜你必须手动检查声明日期和结束日期来改变函数的行为。或者也许通过添加天数而不是月数。（但是我没有add_day在 ref 中找到函数）

Answer 2

回答by Andriy M

As a workaround, I might possibly use this algorithm:

作为一种解决方法，我可能会使用此算法：

Calculate the target date TargetDate1using ADD_MONTHS.
Alternatively calculate the target date TargetDate2like this:
1) apply ADD_MONTHSto the first of the source date's month;
2) add the difference of days between the source date and the beginning of the same month.
Select the LEASTbetween the TargetDate1and TargetDate2.

计算目标日期TargetDate1使用ADD_MONTHS。
或者TargetDate2像这样计算目标日期：
1) 适用ADD_MONTHS于源日期的月份的第一个；
2) 加上源日期和同月初之间的天数差。
LEAST在TargetDate1和之间选择TargetDate2。

So in the end, the target date will contain a different day component if the source date's day component is greater than the number of day in the target month. In this case the target date will be the last day of the corresponding month.

因此，最终，如果源日期的天数大于目标月份的天数，则目标日期将包含不同的天数。在这种情况下，目标日期将是相应月份的最后一天。

I'm not really sure about my knowledge of Oracle's SQL syntax, but basically the implementation might look like this:

我不太确定我对 Oracle 的 SQL 语法的了解，但基本上实现可能如下所示：

SELECT
  LEAST(
    ADD_MONTHS(SourceDate, Months),
    ADD_MONTHS(TRUNC(SourceDate, 'MONTH'), Months)
      + (SourceDate - TRUNC(SourceDate, 'MONTH'))
  ) AS TargetDate
FROM (
  SELECT
    TO_DATE('30-NOV-10', 'DD-MON-RR') AS SourceDate,
    4 AS Months
  FROM DUAL
)

Here is a detailed illustration of how the method works:

以下是该方法工作原理的详细说明：

SourceDate = '30-NOV-10'
Months     = 4

TargetDate1 = ADD_MONTHS('30-NOV-10', 4) = '31-MAR-11'  /* unacceptable */
TargetDate2 = ADD_MONTHS('01-NOV-10', 4) + (30 - 1)
            = '01-MAR-11' + 29 = '30-MAR-11'            /* acceptable */
TargetDate  = LEAST('31-MAR-11', '30-MAR-11') = '30-MAR-11'

And here are some more examples to show different cases:

这里还有一些例子来展示不同的情况：

SourceDate | Months | TargetDate1 | TargetDate2 | TargetDate
-----------+--------+-------------+-------------+-----------
 29-NOV-10 |    4   |   29-MAR-11 |   29-MAR-11 |  29-MAR-11
 30-MAR-11 |   -4   |   30-NOV-10 |   30-NOV-10 |  30-NOV-10
 31-MAR-11 |   -4   |   30-NOV-10 |   01-DEC-10 |  30-NOV-10
 30-NOV-10 |    3   |   28-FEB-11 |   02-MAR-11 |  28-FEB-11

Answer 3

回答by Justin Cave

You can use interval arithmetic to get the result you want

您可以使用区间算法来获得您想要的结果

SQL> select date '2011-03-30' - interval '4' month
  2    from dual;

DATE'2011
---------
30-NOV-10

SQL> ed
Wrote file afiedt.buf

  1  select date '2010-11-30' + interval '4' month
  2*   from dual
SQL> /

DATE'2010
---------
30-MAR-11

Be aware, however, that there are pitfalls to interval arithmetic if you're working with days that don't exist in every month

但是请注意，如果您正在处理每个月不存在的天数，则间隔算术存在缺陷

SQL> ed
Wrote file afiedt.buf

  1  select date '2011-03-31' + interval '1' month
  2*   from dual
SQL> /
select date '2011-03-31' + interval '1' month
                         *
ERROR at line 1:
ORA-01839: date not valid for month specified

Answer 4

回答by tekla

How about something like this:

这样的事情怎么样：

SELECT
    LEAST(
        ADD_MONTHS(TO_DATE('30-MAR-11','DD-MON-RR'),-4),
        ADD_MONTHS(TO_DATE('30-MAR-11','DD-MON-RR')-1,-4)+1
    )
FROM
    DUAL
;

Result: 30-NOV-10

结果：10 年 11 月 30 日

SELECT
    LEAST(
        ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR'),4),
        ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR')-1,4)+1
    )
FROM
    DUAL
;

Result: 30-MAR-11

结果：11 年 3 月 30 日

Answer 5

回答by Tom

Simple solution:

简单的解决方案：

ADD_MONTHS(date - 1, x) + 1

Answer 6

回答by Zolar

Here is the trick:

这是诀窍：

select add_months(to_date('20160228', 'YYYYMMDD')-1, 1)+1 from dual;

Enjoy!

享受！

Answer 7

回答by Orcun Yucel

SELECT TO_DATE('30-NOV-10','DD-MON-RR') + 
       (
        ADD_MONTHS(TRUNC(TO_DATE('30-NOV-10','DD-MON-RR'),'MM'),4) - 
        TRUNC(TO_DATE('30-NOV-10','DD-MON-RR'),'MM')
       ) RESULT
  FROM DUAL;

This section in paranthesis:

括号中的这一部分：

ADD_MONTHS(TRUNC(TO_DATE('30-NOV-10','DD-MON-RR'),'MM'),4) - TRUNC(TO_DATE('30-NOV-10','DD-MON-RR'),'MM')

gives you number of days between the date you entered and 4 months later. So, adding this number of days to the date you given gives the exact date after 4 months.

为您提供从您输入的日期到 4 个月后的天数。因此，将此天数与您给出的日期相加即可得出 4 个月后的确切日期。

Ref: http://www.dba-oracle.com/t_test_data_date_generation_sql.htm

参考：http: //www.dba-oracle.com/t_test_data_date_generation_sql.htm

Answer 8

回答by Sathyajith Bhat

the add_monthsfunction returns a date plus n months.

该add_months函数返回一个日期加上 n 个月。

Since 30th November is the last date of the month, adding 4 months will result in a date that's the end of 4 months. This is expected behavior. If the dates are not bound to change, a workaround is to subtract a day after the new date has been returned

由于 11 月 30 日是该月的最后一天，因此添加 4 个月将导致该日期为 4 个月的结束日期。这是预期的行为。如果日期不一定会更改，则解决方法是在返回新日期后减去一天

SQL> SELECT ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR'),4) -1 from dual;

ADD_MONTH
---------
30-MAR-11

Answer 9

回答by Shaleen Mehta

    CREATE OR REPLACE FUNCTION My_Add_Month(
      STARTDATE           DATE,
      MONTHS_TO_ADD      NUMBER
    )
        RETURN DATE
    IS
        MY_ADD_MONTH_RESULT DATE;
    BEGIN

        SELECT ORACLES_ADD_MONTH_RESULT + NET_DAYS_TO_ADJUST INTO MY_ADD_MONTH_RESULT FROM
        (
            SELECT T.*,CASE WHEN SUBSTRACT_DAYS > ADD_DAYS THEN ADD_DAYS - SUBSTRACT_DAYS ELSE 0 END AS NET_DAYS_TO_ADJUST FROM
            (
                SELECT T.*,EXTRACT(DAY FROM ORACLES_ADD_MONTH_RESULT) AS SUBSTRACT_DAYS FROM
                (
                    SELECT ADD_MONTHS(STARTDATE,MONTHS_TO_ADD) AS ORACLES_ADD_MONTH_RESULT,EXTRACT(DAY FROM STARTDATE) AS ADD_DAYS FROM DUAL
                )T
            )T
        )T;
        RETURN TRUNC(MY_ADD_MONTH_RESULT);
    END My_Add_Month;
    /

    --test & verification of logic & function both
    SELECT T.*,ORACLES_ADD_MONTH_RESULT + NET_DAYS_TO_ADJUST AS MY_ADD_MONTH_RESULT,
    My_Add_Month(STARTDATE,MONTHS_TO_ADD) MY_ADD_MONTH_FUNCTION_RESULT
    FROM
    (
        SELECT T.*,CASE WHEN SUBSTRACT_DAYS > ADD_DAYS THEN ADD_DAYS - SUBSTRACT_DAYS ELSE 0 END AS NET_DAYS_TO_ADJUST FROM
        (
            SELECT T.*,EXTRACT(DAY FROM ORACLES_ADD_MONTH_RESULT) AS SUBSTRACT_DAYS FROM
            (
                SELECT T.*,ADD_MONTHS(STARTDATE,MONTHS_TO_ADD) AS ORACLES_ADD_MONTH_RESULT,EXTRACT(DAY FROM STARTDATE) AS ADD_DAYS FROM
                (
                    SELECT TO_DATE('28/02/2014','DD/MM/YYYY') AS STARTDATE, 1 AS MONTHS_TO_ADD FROM DUAL
                )T
            )T
        )T
    )T;

Query-result

查询结果

STARTDATE 2/28/2014

STARTDATE 2014年2月28日

MONTHS_TO_ADD 1

ORACLES_ADD_MONTH_RESULT 3/31/2014

ORACLES_ADD_MONTH_RESULT 2014年3月31日

ADD_DAYS 28

SUBSTRACT_DAYS 31

NET_DAYS_TO_ADJUST -3

MY_ADD_MONTH_RESULT 3/28/2014

MY_ADD_MONTH_RESULT 2014年3月28日

MY_ADD_MONTH_FUNCTION_RESULT 3/28/2014

MY_ADD_MONTH_FUNCTION_RESULT 2014年3月28日

SQL ADD_MONTHS 函数在 Oracle 中没有返回正确的日期

提问by Mohamed Saligh

采纳答案by M'vy

回答by Andriy M

回答by Justin Cave

回答by tekla

回答by Tom

回答by Zolar

回答by Orcun Yucel

回答by Sathyajith Bhat

回答by Shaleen Mehta

相关推荐

最近更新

标签

SQL ADD_MONTHS 函数在 Oracle 中没有返回正确的日期

提问by Mohamed Saligh

采纳答案by M'vy

回答by Andriy M

回答by Justin Cave

回答by tekla

回答by Tom

回答by Zolar

回答by Orcun Yucel

回答by Sathyajith Bhat

回答by Shaleen Mehta

相关推荐

SQL 在SQL查询中选择多行到一个变量中

where 子句中的 SQL DateDifference

Microsoft SQL 浏览器客户端

SQL 正确查询以获取 PostgreSQL 数据库中的当前连接数

相关推荐

最近更新

标签