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.
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