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!
