Oracle SQL - Months between dates and null values

Question

I am currently trying to get the number of months between 2 dates in Oracle SQL, however when the end date is null then it comes up with null, is there a way to use todays date if end date is null in the select statement?

I have tried the months_between statement, but I am unsure how to handle the null value. If the end date is blank i would like the "months from start" to calculate is as if it was today my current code in select statement is - trunc(months_between(START_DATE,END_DATE)) as "Months From Start"

Current outcome in Picture

My expectation would be the "months from start" column would return -4

Answer 1

Use NVL function (or CASE, or DECODE, ... whichever you find appropriate), e.g.

SQL> with test (event_start, event_end) as
  2    (select date '2022-02-25', date '2022-02-25' from dual union all
  3     select date '2022-02-25', date '2022-08-09' from dual union all
  4     select date '2022-08-09', null              from dual
  5    )
  6  select event_start, event_end,
  7    round(months_between(nvl(event_end, trunc(sysdate)), event_start)) mon_bet
  8  from test
  9  order by 1
 10  /

EVENT_STAR EVENT_END     MON_BET
---------- ---------- ----------
25.02.2022 25.02.2022          0
25.02.2022 09.08.2022          5
09.08.2022                    16

SQL>

BTW, there are 16 months between August 2022 and today (December 2023), not 4.