datetimesql-server-2012windows-10sql-server-native-client

MSSQL query no longer works with Windows 10 client: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

We have been using the following SQL query for a long time in a WinForms program with no problems, until some end users upgraded to Windows 10.

They suddenly get the exception:"ERROR [22007] [Microsoft][SQL Server Native Client 11.0][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

This error has been posted to inside here earlier, but I did not find any post where the occurrence was connected to a Windows 10 upgrade.

The query is targeted to a SQL server 2012, using Native Client 11. It works on windows 7 and 8, but throws exception in Windows 10: 

SELECT DISTINCT tblEmployee.EmployeeID, tblEmployee.Lastname, (COALESCE(tblEmployee.Firstname, '') + ' (' + COALESCE(tblEmployee.EmployeeIDText, '') +')' ) AS Firstname 
FROM tblEmployee 
LEFT JOIN tblAssignmentService ON tblEmployee.EmployeeID = tblAssignmentService.EmployeeID 
WHERE tblAssignmentService.ServiceDate >= '2015-08-31 00.00.00' 
AND tblAssignmentService.ServiceDate < '2015-09-07 00.00.00' 
ORDER BY tblEmployee.Lastname;

The only place where DateTime fields are used is in the Where clause, and the query works fine against the same DB with a windows 8 client. Both clients run Einglish Windows versions. Another interesting observation is that the query is accepted from Microsoft SQL Management Studio on the Windows 10 machine. But not through the native client. The dates used in the filter is created in our program through a GUI.

Have somebody else experienced strange things with Native Client on Windows 10, or does anyone have a suggestion to how this problem may be solved?

Solution

  • I confirm that the solution purposed in this thread solved my problem.

    After some research we found that the ToString("yyyy-MM-dd HH:mm:ss") call responded differently in Windows 7 and Windows 10 if the current culture was set to "no" or "nb-NO" at runtime (Thread.CurrentThread.CurrentCulture = new CultureInfo("no");).

    The suggested modification did the trick: ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture). This solves my problem, as the dots in the time formatting was its root cause.

    But should ToString("yyyy-MM-dd HH:mm:ss") return different formatting in the same culture depending on OS? :O This is kind of scary.