CODEWITHSUNDEEP
sqloracle-database
user7049501
user7049501

Reputation:

Rounding upwards to the nearest month in Oracle DB

I am trying to round upwards to the nearest month. So far, I have:

SELECT ROUND(CURRENT_DATE, 'MM') FROM DUAL

Which rounds to the closest month, which is upwards in this case. At the time of posting, the output is 03/01/2019 in MM/DD/YYYY format.

But what if it's the first of a month for example?

SELECT ROUND(TO_DATE('01-03-19','DD-MM-YY'), 'MM') FROM DUAL

This produces the same output as above. But I am expecting 04/01/2019.

I could do something like:

SELECT TRUNC(ADD_MONTHS(TO_DATE('30-03-19','DD-MM-YY'),1), 'MM') - 1 FROM DUAL

Which produced the output 03/31/2019, which is as expected.

I take the 30th March, add one month onto it. Truncate that, to get the first day of that month, then just subtract one for the last day of the previous month.

Now this works, but it seems long and tedious. Surely there is a better way?

Upvotes: 2

Views: 467

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30663

if you are trying to find last day of the current month

LAST_DAY(SYSDATE)

if you are trying to find first day of the next month

LAST_DAY(SYSDATE) + 1

Upvotes: 2

Related Questions