Convert month and year as last day of the month in oracle sql

I have two column – Month and Year in a table.

Need to concatenate these to get the last day of the month in date format. For example, if Month =5 and Year =2020, the output needs to 31-May-20.

Similarly if Month =4 and Year=19, the output needs to be 30-APR-20.

Any idea how this can be done, is there any analytical functions in Oracle SQL that I can use to create this output.

DB version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0.

Answer

As years don’t have the same number of digits,

• left pad them with 20 (as it seems you want this century),
• concatenate them with months (do the same – left pad them with 0 to get the full YYYYMM format, and
• use LAST_DAY function to get the final result

Something like this (I’m altering the session as my database speaks Croatian and format mask differs from yours) (sample data first; query you might need begins at line #5):

SQL> alter session set nls_date_language = english;

Session altered.

SQL> alter session set nls_date_format = 'dd-mon-yyyy';

Session altered.

SQL> with test (mon, yr) as
  2    (select '5', '2020' from dual union all
  3     select '4',   '19' from dual
  4    )
  5  select mon,
  6         yr,
  7         last_day(to_date(lpad(yr, 4, '20') || lpad(mon, 2, '0'), 'yyyymm')) result
  8  from test;

M YR   RESULT
- ---- -----------
5 2020 31-may-2020
4 19   30-apr-2019

SQL>