CODEWITHSUNDEEP
oracle-database
Anand Jagtap
Anand Jagtap

Reputation: 85

Combination of add_months and to_date gone wrong if date is 28-Feb

I'm trying to get a date of 16 days before of 6 months + given date. Now that's a simple task. I've created a query to get the result. But yesterday I entered 28-Feb-2019 and answer gone wrong. I tried to find solution but failed. So please, help me out I'm providing two saperate queries to find the answer.

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:P2_JOIN_DT,'DD-MON-RRRR')-16,6)) FROM DUAL;

Execute above query and compare date 01-MAR(or any march date) with 01_JAN(or any other month). Need to get value 16-(month + 6).

SELECT TO_CHAR(TO_DATE(ADD_MONTHS(TO_DATE(:P2_JOIN_DT,'DD-MON-RRRR'),6)-16)) FROM DUAL;

Now, execute this query and compare 28-FEB with any 28-(month).

Other dates are working right.

Actually what is happening, I've created an oracle-apex web-application where, after every month (and one day before) user can give ratings for a particular task (this dates are also displayed on screen). Task is simple so whenever this due date occures field becomes editable otherwise it will be read-only or disabled. But after every 6th month, field must be enable before 15 days of displayed due date. Now here, task gets quite confusing. because finding 1 day early date makes sence (in case of 28-FEB, I get 27) but after 6th month where displayed due date is 27-aug field has to be enabled on 12-aug (15 days before of displayed due date). So, for displaying I used normal query by subtracting only 1 day, but for enablling field I've to subtract 16 days on 6th month.

So that's why I've provided 2 queries. one of them works for 28-feb but if date is 1-march then query fails, and another works for 1-march but failed on 28-feb

But this causes a fault in my system.

Please help me to get rid out of this.

Upvotes: 0

Views: 920

Answers (4)

Popeye
Popeye

Reputation: 35900

I think to avoid such problem you can add months in truncated date to month and adding days again like following:

add_months(trunc(:P2_JOIN_DT, 'month'), 6) + extract(day from :P2_JOIN_DT) + 16

Cheers!!

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

Your first query is subtracting 16 days (which may take you into the previous month) and then adds six months to that. Your second query add six months (which seems to be tripping you up at month end, as other have pointed out.

You seem to want to different queries at different points in the month; so you could combine both calculations in a case expression:

case when extract(day from p2_join_dt) > 16
  then
    add_months(p2_join_dt - 16, 6)
  else
    add_months(p2_join_dt, 6) - 16
end

If the original date is after the 16th day of the month, then subtracting 16 days leaves you in the same month, so you can then add six months to that. If the original date is on or before the 16th then subtracting 16 days would put you in the previous month, which you want to avoid (I think!); but for those you can add six months first, and then do the subtraction.

Demo with some sample dates of the 1st and 28th of each month this year, and showing the results of both your original queries separately for comparison:

with cte (p2_join_dt) as (
  select date '2019-01-01' + (level - 1) * interval '1' month
  from dual
  connect by level <= 12
  union all
  select date '2019-01-28' + (level - 1) * interval '1' month
  from dual
  connect by level <= 12
)
select p2_join_dt,
  add_months(p2_join_dt - 16, 6) as query1,
  add_months(p2_join_dt, 6) - 16 as query2,
  case
    when extract(day from p2_join_dt) > 16 then
      add_months(p2_join_dt - 16, 6)
    else
      add_months(p2_join_dt, 6) - 16
  end as combined
from cte
order by p2_join_dt;

which gives:

P2_JOIN_DT QUERY1     QUERY2     COMBINED  
---------- ---------- ---------- ----------
2019-01-01 2019-06-16 2019-06-15 2019-06-15
2019-01-28 2019-07-12 2019-07-12 2019-07-12
2019-02-01 2019-07-16 2019-07-16 2019-07-16
2019-02-28 2019-08-12 2019-08-15 2019-08-12
2019-03-01 2019-08-13 2019-08-16 2019-08-16
2019-03-28 2019-09-12 2019-09-12 2019-09-12
2019-04-01 2019-09-16 2019-09-15 2019-09-15
2019-04-28 2019-10-12 2019-10-12 2019-10-12
2019-05-01 2019-10-15 2019-10-16 2019-10-16
2019-05-28 2019-11-12 2019-11-12 2019-11-12
2019-06-01 2019-11-16 2019-11-15 2019-11-15
2019-06-28 2019-12-12 2019-12-12 2019-12-12
2019-07-01 2019-12-15 2019-12-16 2019-12-16
2019-07-28 2020-01-12 2020-01-12 2020-01-12
2019-08-01 2020-01-16 2020-01-16 2020-01-16
2019-08-28 2020-02-12 2020-02-12 2020-02-12
2019-09-01 2020-02-16 2020-02-14 2020-02-14
2019-09-28 2020-03-12 2020-03-12 2020-03-12
2019-10-01 2020-03-15 2020-03-16 2020-03-16
2019-10-28 2020-04-12 2020-04-12 2020-04-12
2019-11-01 2020-04-16 2020-04-15 2020-04-15
2019-11-28 2020-05-12 2020-05-12 2020-05-12
2019-12-01 2020-05-15 2020-05-16 2020-05-16
2019-12-28 2020-06-12 2020-06-12 2020-06-12

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Oracle documentation:

If date is the last day of the month (...) then the result is the last day of the resulting month.

So:

select add_months(date '2019-02-28', 6) from dual

returns date 2019-08-31, not 2019-08-28 as you expected.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

"6 months + given date" is

add_months(given_date, 6)

"16 days before of 6 months + given date" is

add_months(given_date, 6) - 16

For example:

SQL> select add_months(date '2019-02-28', 6) - 16 res1,
  2         add_months(date '2019-03-28', 6) - 16 res2
  3  from dual;

RES1       RES2
---------- ----------
15.08.2019 12.09.2019

SQL>

Upvotes: 0

Related Questions