Forum Discussion
RDL Error
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
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!
I have tried the suggestions but still get the error
Hello,
If you have where I Can share the file with you to have a look that will be fine.
It is an excel file. Column AC- Projected date has a date 10/15/1810 which is wrong. I can share the Case statement for amendment if it is a problem.
Column AD is a date and Days(numbers). it has a number 22,423.
Below are the Case Statements
Column AC- case statementSELECT CASE
WHEN SOLUTION2_DISBURSED_AMT > 0
AND SOLUTION2_DISBURSED_AMT < 0.98 * CLOSING_SOLUTION2_AWARD_AMT THEN
-- Add a cap (e.g., 50 years) so it doesn't crash the report
GRANT_EXECUTION_DT + NUMTODSINTERVAL(
LEAST(
ROUND((DATE '2024-06-14' - GRANT_EXECUTION_DT) /
(SOLUTION2_DISBURSED_AMT / NULLIF(CLOSING_SOLUTION2_AWARD_AMT, 0)), 0),
18250 -- Caps the addition at 50 years
), 'DAY')
WHEN GRANT_EXECUTION_DT IS NOT NULL AND SOLUTION2_DISBURSED_AMT IS NULL THEN
GRANT_EXECUTION_DT + 365
ELSE NULL
END AS PROJECTED_DATEfrom table
COLUMN AD CASE STATEMENT-- USED FOR DATE OF COMPLETION COUNT_PROJ
SELECT CASE WHEN BUCKET2='IN WORK' AND "Used for Date of Completion Count" IS NOT NULL THEN "Used for Date of Completion Count"
ELSE NULL END AS "Used for Date of Completion Count_ProJ"FROM TABLE
-----------------------------------------------
Used for date of completion count is calculated as
SELECT
CASE
WHEN GRANT_EXECUTION_DT IS NOT NULL
AND SOLUTION2_DISBURSED_AMT IS NOT NULL
AND SOLUTION2_DISBURSED_AMT < 0.98 * CLOSING_SOLUTION2_AWARD_AMT THEN
CASE
WHEN ROUND((DATE '2024-06-14' - GRANT_EXECUTION_DT) /
(SOLUTION2_DISBURSED_AMT / NULLIF(CLOSING_SOLUTION2_AWARD_AMT, 0))) < 365
THEN TO_CHAR(GRANT_EXECUTION_DT + 365, 'MM/DD/YYYY')
-- CAST THE NUMBER TO CHAR TO MATCH THE OTHER BRANCH
ELSE TO_CHAR(ROUND((DATE '2024-06-14' - GRANT_EXECUTION_DT) /
(SOLUTION2_DISBURSED_AMT / NULLIF(CLOSING_SOLUTION2_AWARD_AMT, 0))))
END
ELSE NULL
END AS "Used for Date of Completion Count"FROM TABLE
