MSSQL: System error opening projects with finish date far in future
Article ID: 282645
Updated On:
Products
Issue/Introduction
When the finish date of project/task is updated to a large value in the future the project becomes inaccessible in the environments configured with MS SQL database
Steps to Reproduce:
- Navigate to New UI -> Projects Grid Layout
- Update the finish date of the Project as 2424. A warning is displayed stating 'valid max date is Dec 31, 2099'
- Open the Project
- Change the Finish date of the Project to a value of 3/03/2424 (Here the year is sometimes mistakenly updated as 2424 instead of 2024)
Expected Results:
The system should not allow the finish date to be updated to that high value. A warning should be displayed instead of showing the max limit.
Actual Results:
The finish date is updated to 2424 and the project becomes inaccessible later
app-ca.log file shows following error:
ERROR 2020-10-05 11:39:16,455 [http-nio-80-exec-675] union.persistence (clarity:<user>:<session>:projmgr.projectProperties) Error closing statement
java.sql.SQLDataException: [CA Clarity][SQLServer JDBC Driver][SQLServer]The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
at com.ca.clarity.jdbc.sqlserverbase.ddco.a(Unknown Source)
Environment
All Clarity releases
Database: MS SQL
Cause
This was analyzed by Engineering through DE80561
Resolution
This is not a product issue. It is a limitation in this function (datediff) specific for SQL Server. The query itself is OOTB, but it is based on a SQL Server function. When a big interval is used, e.g., 2424, it throws the error message.
Additional Information
The date difference is not only affected by dates in the future, if a Project was dated in the far past a similar problem could occur. The problem occurs based on the total range of the Open and Closed dates, and isn't specific to a particular date or time.
Feedback
