Symptom
- Adhoc report will produce blank results (thru Run Online) while setting the maximum date range of 12/31/9999 in the runtime filter
- Run the adhoc report offline (thru excel) and the file will produce the error
Error encountered in executing the query.[303]: invalid DATE, TIME or TIMESTAMP value: year must be between -4713 and +9999, and not be 0: 10000: type_code=14, index=2
Environment
SAP Success Factors Workforce Analytics (ANA) - All versions
Reproducing the Issue
- Run any adhoc reports online using a maximum date of 12/31/9999 in the runtime filter
- The report will time out (run for more than 5 minutes) or it will display blank results
- Run the adhoc report offline (thru excel) and the file will produce an error
Cause
In the coding internally, any date inputted is being incremented by 1. If the date filter is set as 12/31/9999, the system will acknowledge this as 01/01/10000.
Resolution
The Date should be 30-Dec-9999 or earlier because in the code the date is being incremented by 1 and 31-Dec-9999 becomes 1-Jan-10000 which is an invalid year so it throws invalid year exception.
Keywords
12/31/9999, adhoc runtime filter, date filter, open end date , KBA , LOD-SF-ANA-ADH , Adhoc Reports & Report Builder , Problem