topic Re: RDL Error in Report Server

RDL Error

mjsystemss — Mon, 13 Apr 2026 17:30:19 GMT

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.

Re: RDL Error

Zanqueta — Mon, 13 Apr 2026 19:18:22 GMT

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 0
occurs 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:

CASE
    WHEN REGEXP_LIKE(App_Submitted_DT, '^\d{2}/\d{2}/(19|20)\d{2}$')
    THEN TO_DATE(App_Submitted_DT, 'MM/DD/YYYY')
    ELSE NULL
END 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.

mjsystemss

Re: RDL Error

grazitti_sapna — Tue, 14 Apr 2026 05:38:15 GMT

Hi 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!

Re: RDL Error

cengizhanarslan — Tue, 14 Apr 2026 06:31:42 GMT

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_Date

Re: RDL Error

mjsystemss — Tue, 14 Apr 2026 12:30:03 GMT

I have tried the suggestions but still get the error

Re: RDL Error

mjsystemss — Wed, 15 Apr 2026 14:37:01 GMT

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 statement

SELECT 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_DATE

from 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

Re: RDL Error

grazitti_sapna — Thu, 16 Apr 2026 04:12:30 GMT

Hi mjsystemss,

Did you get a chance to try this solution?

Re: RDL Error

v-aatheeque — Fri, 17 Apr 2026 10:51:18 GMT

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.

Re: RDL Error

mjsystemss — Mon, 20 Apr 2026 14:55:01 GMT

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.

Re: RDL Error

mjsystemss — Mon, 20 Apr 2026 14:56:34 GMT

The problem was the date range ssrs can handle. So any date or days more than 100 years gives an error. The column is a projected date column.

Re: RDL Error

v-aatheeque — Tue, 21 Apr 2026 09:46:41 GMT

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!