CODEWITHSUNDEEP
sqloracle-database
ahmed
ahmed

Reputation: 45

How to add a quarter to a date in Oracle

I am looking for a way in oracle to add a quarter to a date.

For e.g 28-FEB-21 should become 28-MAY-20

Currently i am using ADD_MONTHS('28-FEB-21',3) to add a quarter but its changing the date to 31-MAY-21

Regards,

Upvotes: 0

Views: 1359

Answers (1)

MT0
MT0

Reputation: 168740

You can use:

LEAST(
  ADD_MONTHS( value, 3 ),
  ADD_MONTHS( TRUNC( value, 'MM' ), 3 )
    + ( value - TRUNC( value, 'MM' ) ) DAY TO SECOND
)

So, if you have the test data:

CREATE TABLE table_name ( value ) AS
SELECT DATE '2019-11-30' FROM DUAL UNION ALL
SELECT DATE '2021-02-28' + INTERVAL '01:23:45' HOUR TO SECOND FROM DUAL;

Then the query:

SELECT value,
       LEAST(
         ADD_MONTHS( value, 3 ),
         ADD_MONTHS( TRUNC( value, 'MM' ), 3 )
           + ( value - TRUNC( value, 'MM' ) ) DAY TO SECOND
       ) AS value_plus_quarter
FROM   table_name;

Outputs:

VALUE               | VALUE_PLUS_QUARTER 
:------------------ | :------------------
2019-11-30 00:00:00 | 2020-02-29 00:00:00
2021-02-28 01:23:45 | 2021-05-28 01:23:45

db<>fiddle here

Upvotes: 1

Related Questions