Sorting workqueue or job queries based on a custom date work type field results in a database error

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