to_date的转换表示缺少右括号

Question

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;

错误："FROM关键字未在预期的"处找到；

Answer 1

很多事情。

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>

---

尽管看起来你想要当月的1号，但你可以将其缩短为

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>

---

LAST_DAY选项：

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>