RDL Error

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!