search cancel

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

book

Article ID: 200962

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

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

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

DATEDIFF (Transact-SQL)