Oracle SQL中TO_DATE转换报错与查询语句FROM关键字位置错误求助

Hey there, let's work through these two Oracle SQL errors you're hitting—they're both common issues with straightforward fixes:

This error usually pops up for a few key reasons:

• Mismatched parentheses: You might have forgotten a closing parenthesis for the TO_DATE function itself, or for a nested function (like LPAD) used inside it.
• Incorrect number of arguments: TO_DATE requires at least two parameters: the string you want to convert, and the format model that matches that string. If you only pass one, Oracle expects more input and throws this error.
• Invalid format model or quotes: Using Chinese single quotes instead of English ones, or typos in your format model (like writing YYYYY instead of YYYY) can also trigger this issue.

Fixes: #

• Double-check all parentheses: Count left and right parentheses to make sure they pair up. For example, if you wrote TO_DATE('2023-12-01', 'YYYY-MM-DD', add the missing closing ).
• Ensure you're passing both required arguments: A valid call looks like TO_DATE('2023-12', 'YYYY-MM')—not TO_DATE('2023-12').
• Stick to English quotes and valid format codes: Use 'YYYY-MM-DD' (not Chinese quotes) and verify your format codes match Oracle's standards (e.g., MM for 2-digit months, DD for 2-digit days).

Looking at your query, the root cause is almost certainly that year and month are Oracle reserved keywords/built-in function names. Oracle interprets these as function calls (like YEAR() which extracts the year from a date) instead of column names in your test table. This breaks the syntax of your SELECT clause—Oracle expects a parameter after YEAR() instead of a string concatenation (||), so it can't parse the rest of the statement correctly, leading to the "FROM not found" error.

Fix: #

Wrap the reserved-word column names in double quotes to explicitly tell Oracle they're column references, not functions. Here's your corrected query:

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;

Bonus optimization: #

To avoid repeating the same date calculation twice, use a CTE (Common Table Expression) to generate the base date string once:

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

内容的提问来源于stack exchange，提问作者Karuna