Forum Discussion
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_Submitted_DT, TO_DATE(Curr_Application_Status_DT DEFAULT NULL ON CONVERSION ERROR, 'MM/DD/YYYY') AS Curr_Application_Status_DT, CAST(Grant_Execution_Date AS DATE) AS Grant_Execution_Date
FROM YOUR_TABLE_NAME yet the problem persists. I need some advise on what to do.
ERROR
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
----------------------------
Cannot read the next data row for the dataset 'EVMS_Detail_Tab'.
----------------------------
An error has occurred during report processing.
----------------------------
An error occurred during local report processing.
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.
10 Replies
Replace CAST with TO_DATE on Grant_Execution_Date:
TO_DATE( TO_CHAR( Grant_Execution_Date ), 'MM/DD/YYYY' DEFAULT NULL ON CONVERSION ERROR ) AS Grant_Execution_DateHi mjsystemss,
Issue is with the date expression and not with Power BI or RDL
Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0, is thrown when Your data contains invalid values for year = 0 or a bad input string.
Try this query and validate the data before conversion
SELECT
CASE
WHEN VALIDATE_CONVERSION(App_Submitted_DT AS DATE, 'MM/DD/YYYY') = 1
THEN TO_DATE(App_Submitted_DT, 'MM/DD/YYYY')
ELSE NULL
END AS App_Submitted_DT
FROM YOUR_TABLE_NAME;Alternatively you can also try with Regex
SELECT
CASE
WHEN REGEXP_LIKE(App_Submitted_DT, '^\d{2}/\d{2}/\d{4}$')
AND SUBSTR(App_Submitted_DT, 7, 4) <> '0000'
THEN TO_DATE(App_Submitted_DT, 'MM/DD/YYYY')
ELSE NULL
END AS App_Submitted_DT,CASE
WHEN REGEXP_LIKE(Curr_Application_Status_DT, '^\d{2}/\d{2}/\d{4}$')
AND SUBSTR(Curr_Application_Status_DT, 7, 4) <> '0000'
THEN TO_DATE(Curr_Application_Status_DT, 'MM/DD/YYYY')
ELSE NULL
END AS Curr_Application_Status_DT,Grant_Execution_Date
FROM YOUR_TABLE_NAME;
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi mjsystemss
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
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!
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
I have tried the suggestions but still get the error
