0

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

Improve this question

1 Answer 1

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for picking that up, it must have been a long day yesterday hahaha. Thank you so much, that worked perfectly for me. Solved!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.