SQL Server datediff function overflow when date/time instances is too large
Article ID: 200962
Updated On:
Products
Issue/Introduction
A project has the scheduled start date set to 2002/01/01. If setting the finish date to 2099/12/31, it throws the following error message:
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)
It may not be possible to edit the Project, and the Load Data Warehouse job may fail.
Environment
Clarity PPM 15.9.3
Cause
It 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 very big interval is used, e.g., 2099, it throws the error message.
Resolution
It will allow till 2079, not more.
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.
Additional Information
Feedback
