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"
My expectation would be the "months from start" column would return -4
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.