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 th...

问题:

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?


回答1:

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
  • 发表于 2019-02-26 21:47
  • 阅读 ( 199 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除