search cancel

SQL Server datediff function overflow when date/time instances is too large


Article ID: 200962


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


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:admin:16896349__53D67B7D-6583-4167-878C-8DD5AF80DB65: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 Source)

It may not be possible to edit the Project, and the Load Data Warehouse job may fail.


Clarity PPM 15.8.1


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.


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