Forum Discussion
Solved
RDL Error
Hello, I have been getting this error on rdl file since today. I have tried to format the date columns SELECT TO_DATE(App_Submitted_DT DEFAULT NULL ON CONVERSION ERROR, 'MM/DD/YYYY') AS App_Submit...
This has been resolved. SSRS has date range it can handle. As a column contains date and days. Dates outside 100 years bound will give an error.
Hi
I am not completely certain, but the problem is not mainly related to date formatting. It is caused by invalid date values in Oracle, which the RDL / SSRS report cannot process when reading the dataset.
The error:
ORA‑01841: (full) year must be between -4713 and +9999, and not be 0occurs when Oracle tries to convert a value that contains an invalid year, most commonly:
- Dates with year 0000
- Values such as 00/00/0000 or 01/01/0000
- Non-date strings like N/A or other invalid placeholders
Oracle does not support year 0, and a single invalid row is enough to cause the entire dataset to fail in SSRS.TO_DATE(column DEFAULT NULL ON CONVERSION ERROR, 'MM/DD/YYYY')The most reliable approach is to validate the value before converting it.Example:
CASEWHEN REGEXP_LIKE(App_Submitted_DT, '^\d{2}/\d{2}/(19|20)\d{2}$')THEN TO_DATE(App_Submitted_DT, 'MM/DD/YYYY')ELSE NULLEND AS App_Submitted_DT
Why This Started Recently:
- A new record with invalid date data
- A change in the source system or ETL process
Even one invalid date value can cause the report to fail.
This has been resolved. SSRS has date range it can handle. As a column contains date and days. Dates outside 100 years bound will give an error.
Hi
Glad to hear the issue is resolved! Thanks for sharing the root cause the SSRS date range limitation is a helpful insight.
If any of the suggested solutions helped, please feel free to acknowledge them it will be beneficial for others facing similar scenarios.
Appreciate you confirming this, and do reach out if anything else comes up!
