When browsing Sessions, Logon Performance or Capacity Management tab under Citrix Director > Trends, user get a chance to suffer error, "Cannot retrieve the data. Data source unresponsive or reported an error."

Below exception is seen on the CDF trace, 

OData API Bad Request: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: 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.

To workaround the limitation, it is suggested to contact Citrix Support to figure out which records in which Monitor Database table contain two dates with gap larger than 596.5 Hour and manually remove these unexpected records. Then the Director page should recover.

---

Problem Cause

[Limitation]
1. Under Director > Trends panel, duration metrics displayed under Sessions tab, Logon Performance tab and Capacity Management tab all come down to DateDiff calculation, like Session duration, Application usage duration and Logon duration.

2. In Citrix Director design, to obtain the above duration metrics, SQLServer function DateDiff is invoked to calculate gap between two dates in Millisecond. Since return value of DateDiff function is INT32 type, the maximum supported value is + 2,147,483,647. If transferring 2,147,483,647 Millisecond into Hour, it equals to around 596.5 Hours. In other word, if two dates' gap is larger than 596.5 Hours, when Monitor Service calculates their gap, DateDiff function will result in data overflow and Director page won't properly display.