如何解决SQL错误ORA-01843：无效月份？更新语句报错求助

Hey Lara, the ORA-01843 error you're hitting is all about date format mismatch between your input string and Oracle's default date parsing rules. Let's break this down and get your UPDATE working.

Why the Error Occurs #

Oracle relies on a default date format (usually something like DD-MON-RR or MM/DD/YYYY, depending on your database's NLS configuration) to convert string values into date types. Your date string '23/4/2018 12:00:00 AM' doesn't align with that default format—Oracle can't correctly interpret the 23/4 portion as a valid month/day pair under its current settings, hence the "not a valid month" error.

Solution 1: Use TO_DATE() to Explicitly Define the Format #

The most reliable fix is to use Oracle's TO_DATE() function to tell the database exactly how your date string is structured. For your input, the format mask should be 'DD/MM/YYYY HH:MI:SS AM' (since you're using day/month/year, followed by 12-hour time with an AM/PM indicator).

Here's your corrected UPDATE statement:

UPDATE Table1 
SET CHANGE_DELIVERY_REASON = 'Card Activation', 
    CHANGE_DELIVERY_NOTE = 'ACTIVATION SUCCESS [1 OK - 2 OK]', 
    RECONCILIATION_STATUS = 1 , 
    RECONCILIATION_LAST_UPDATED = TO_DATE('23/4/2018 12:00:00 AM', 'DD/MM/YYYY HH:MI:SS AM'), 
    RECONCILIATION_COUNT = '6', 
    ACTIVATION_FAILURE = 'SUCCESS';

Solution 2: Adjust Session Date Format (For One-Off Runs) #

If you have permission to modify your session settings, you can temporarily set the NLS date format to match your input string before executing the UPDATE:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH:MI:SS AM';

Then run your original UPDATE statement. Keep in mind this change only applies to your current session—once you log out, the format reverts to the default. This isn't ideal for production scripts, but it works for quick, one-time updates.

Quick Tips #

• Double-check that your format mask matches the string exactly: DD for day, MM for numeric month, YYYY for 4-digit year, HH for 12-hour time, MI for minutes, SS for seconds, and AM for the period indicator.
• If you were using 24-hour time (e.g., 00:00:00 instead of 12:00:00 AM), swap HH for HH24 and remove the AM part from the format mask.

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