ORACLE displaying date field with 1 more day at the end of the months with 31 days

Asked

Viewed 54 times

0

I created a function in ORACLE to calculate the date of the last installment by passing as parameter the number of installments and using the function ADD_MONTHS(DT_PRIM_PARCELA, INSTALLMENTS).

However, if I pass a date for example ADD_MONTHS('30/11/2020, 96 - 1), the date of the last installment should be day (30/10/2028), however, ORACLE is returning (31/10/2028).

This is occurring whenever the date of the last installment falls in a month with 31 days.

This is standard ORACLE or is it a matter of configuration?

SELECT D.NR_PROSPECT,
       D.DT_PRIM_PARC,
       D.PRAZO,
       ADD_MONTHS('30/11/2020', 96 - 1) AS DATA_ULT_VENCIMENTO
  FROM TB_PROSPECT_CREDITO D
 WHERE NR_PROSPECT = 1490;

database oracle datetime pl-sql

  • Try the following: SELECT LEAST(ADD_MONTHS(to_date('30/11/2020', 'dd/mm/yyyy'),96), ADD_MONTHS(to_date('30/11/2020', 'dd/mm/yyyy')-1,96)+1) as foo FROM DUAL;

  • https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions077.htm#SQLRF00654

1 answer

0


The documentation of the function itself ADD_MONTHS quote:

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

Translating, if the date is the last day of the resulting month or month has fewer days than the last date, then the result will be the last day of the month. Otherwise the result will have the same day.

Browser other questions tagged database oracle datetime pl-sql

You are not signed in. Login or sign up in order to post.