Sorting workqueue or job queries based on a custom date work type field results in a database error
Article # 3047997 - Page views: 13
Issue
Sorting a query based on a date work type field results in the below error.
The error logged in the event viewer will have the inner exception below (translated to English).
Converting an nvarchar data type to a datetime data type created an out-of-bounds value
Cause
By default, all work type fields are stored as strings in the TotalAgility database. These are then cast to the correct data type when required.
When the SQL user is configured to use a non-English language, certain dates e.g 2022-07-26 fail to be converted as the "26" is viewed as the month.
Solution
The solution is to configure the SQL users default language as English.
This is a known issue due to be fixed in v7.11.
Level of Complexity
Moderate
Applies to
Product | Version | Build | Environment | Hardware |
---|---|---|---|---|
KTA | 7.8+ |