Conversion of to_date says missing right parenthesis-CodePudding

select A,B,C,
TO_DATE(year  ||'-'||  LPAD(month,2,0)  ||'-01','YYYY-MM-DD') as firstday,
LAST_DAY(TO_DATE(year  ||'-'||  LPAD(month,2,0)  ||'-01','YYYY-MM-DD')) as lastday
from test;

Error : "FROM keyword not found where expected"

CodePudding user response：

Whole lot of things.

SQL> with test (year, month) as
  2    (select '2022', '4' from dual)
  3  select to_date(year ||'-'|| lpad(month, 2, '0') || '-01', 'yyyy-mm-dd') result
  4  from test;

RESULT
--------------------
01-APR-22

SQL>

Though, as it seems you want the 1st of that month, you can shorten it to

SQL> with test (year, month) as
  2    (select '2022', '4' from dual)
  3  select to_date(year || month, 'yyyymm') result
  4  from test;

RESULT
--------------------
01-APR-22

SQL>

The LAST_DAY option:

SQL> with test (year, month) as
  2    (select '2022', '4' from dual)
  3  select
  4             to_date(year  ||'-'||  lpad(month,2,0)  ||'-01','YYYY-MM-DD')  as firstday,
  5    last_day(to_date(year  ||'-'||  lpad(month,2,0)  ||'-01','YYYY-MM-DD')) as lastday
  6  from test;

FIRSTDAY   LASTDAY
---------- ----------
01.04.2022 30.04.2022

SQL>