How to Debug Date Related Problems Like (but not Limited to) ORA-01843, ORA-01821, ORA-1801
(Doc ID 338832.1)
Last updated on JUNE 13, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 8.1.7.4 and laterOracle Database - Standard Edition - Version 8.1.7.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Purpose
Help to debug date related problems like ORA-01843, ORA-01821, ORA-1801.
Scope
Anyone encountering an error that is date related.
Details
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Purpose |
| Scope |
| Details |
| 1) Common reasons for ORA-01843: not a valid month or ORA-01821: date format not recognized |
| 1.A) using a wrong date format mask in your code |
| 1.B) Using implicit date format masks (correct mask is set at "database level" but not used). |
| 1.C) Using TO_DATE on an text string. |
| 2) General steps to debug the error message: |
| 2.A) Compare Listener and database versions. |
| 2.B) Check ORA_NLSxx |
| 2.C) Set an event to know on what statement the error is happening. |
| 2.C.1)To define an event non persistent (=gone after restart of database) but no need to restart the database: |
| 2.C.2) To define the event persistent: |
| 3) Checking the client's settings. |
| 4) When using JDBC. |
| 5) Using after logon triggers. |
| 5.A) To enforce a value from the server side. |
| 5.B) To log clients settings. |
| 6) Getting wrong result (for example year 0014 is stored when string is using YY format like 14 ) of implicit or explict cast to a date dataype |
| 7) errors like ORA-01830: date format picture ends before converting entire input string (or other ora-018xx errors) after upgrade of the database |
| 8) Other possible causes (less likely): |
| 9) Known bugs: |
| 10) List of all ora-018xx errors and their event , usefull to set if you have "random" date errors |
| References |