Python执行Oracle SQL时日期输入报错：无效月份

Got it, let's break down why you're hitting that "not a valid month" error and how to fix it quickly.

The core issue here is that Oracle doesn't know how to interpret the YYYY-MM-DD format you're passing—its default date parser expects a different format, and your DAY_DESC column is displayed/stored as MM/DD/YYYY. Let's fix this properly:

First, Use Date-to-Date Comparisons (Not Strings!) #

If DAY_DESC is a DATE type (even if it shows up as MM/DD/YYYY in your tooling), you should convert your user input to a DATE directly instead of messing with string conversions. This avoids format mismatches and keeps your query efficient (it can use indexes on DAY_DESC).

Here's the corrected query using parameter binding (always use this instead of string interpolation to avoid SQL injection and format bugs):

# Example user inputs in YYYY-MM-DD format
start_date = "2024-02-01"
end_date = "2024-02-29"
io_id = 12345

# Corrected SQL query with proper date conversion
query = """
SELECT substr(PLACEMENT_DESC, 1, INSTR(PLACEMENT_DESC, '.', 1) - 1) AS "Placement#",
       SUM(VIEWS) AS "Delivered_Impresion",
       SUM(CLICKS) AS "Clicks",
       SUM(CONVERSIONS) AS "Conversion"
FROM TFR_REP.DAILY_SALES_MV
WHERE IO_ID = :io_id
  AND DAY_DESC BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') 
                   AND TO_DATE(:end_date, 'YYYY-MM-DD')
GROUP BY substr(PLACEMENT_DESC, 1, INSTR(PLACEMENT_DESC, '.', 1) - 1)
"""

# Execute with parameter binding (using cx_Oracle or your Oracle driver of choice)
cursor.execute(query, io_id=io_id, start_date=start_date, end_date=end_date)

Key Fixes Here: #

• TO_DATE(:start_date, 'YYYY-MM-DD'): Explicitly tells Oracle your input string uses YYYY-MM-DD format, so it parses it correctly instead of guessing (which causes the "invalid month" error).
• Parameter binding: Using :io_id, :start_date etc. instead of string interpolation keeps your code secure and avoids accidental format issues from concatenating strings.

If DAY_DESC is a String Column (VARCHAR2) #

If DAY_DESC is stored as a string (not a DATE type), you'll need to convert it to a DATE first to compare properly (string comparisons can lead to weird bugs—like '12/31/2023' > '01/01/2024' being false as strings, but true as dates). Adjust the query like this:

query = """
SELECT substr(PLACEMENT_DESC, 1, INSTR(PLACEMENT_DESC, '.', 1) - 1) AS "Placement#",
       SUM(VIEWS) AS "Delivered_Impresion",
       SUM(CLICKS) AS "Clicks",
       SUM(CONVERSIONS) AS "Conversion"
FROM TFR_REP.DAILY_SALES_MV
WHERE IO_ID = :io_id
  AND TO_DATE(DAY_DESC, 'MM/DD/YYYY') BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') 
                                          AND TO_DATE(:end_date, 'YYYY-MM-DD')
GROUP BY substr(PLACEMENT_DESC, 1, INSTR(PLACEMENT_DESC, '.', 1) - 1)
"""

What to Avoid: #

• Don't convert DAY_DESC to a string with TO_CHAR for comparisons. This breaks any indexes on DAY_DESC and slows down your query.
• Never hardcode dates or use string interpolation (like f"WHERE DAY_DESC BETWEEN '{start_date}'"). This is a security risk and prone to format errors.

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