问题描述
我们一直在WinForms程序中长时间使用以下SQL查询，没有任何问题，直到某些最终用户升级到Windows 10.
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.
他们突然得到例外：ERROR [22007] [Microsoft] [SQL Server Native Client 11.0] [SQL Server]将varchar数据类型转换为datetime数据类型导致超出范围的值。
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."
此错误已经发布到此处的内部，但我没有发现任何发生连接到Windows 10升级的帖子。
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.
该查询针对的是SQL Server 2012，使用Native Client 11.它适用于Windows 7和8，但在Windows 10中抛出异常：
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;
仅使用DateTime字段的地方在Where子句中，查询对于同一个数据库与Windows 8客户端。两个客户端都运行Einglish Windows版本。另一个有趣的观察是，该查询从Windows 10机器上的Microsoft SQL Management Studio接受。但不能通过本机客户端。过滤器中使用的日期是通过GUI在我们的程序中创建的。
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.
有人在Windows 10上使用Native Client进行奇怪的事情，还是有人建议如何解决这个问题？
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?
推荐答案
我确认解决了我的问题。
I confirm that the solution purposed in this thread solved my problem.
经过一些研究，我们发现 ToString（yyyy-MM-dd HH：mm：ss）如果当前文化在运行时设置为no或nb-NO，则调用Windows 7和Windows 10的响应不同（ Thread.CurrentThread.CurrentCulture = new CultureInfo（no）; ）。
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");).
建议的修改功能是： ToString（yyyy-MM-dd HH：mm：ss CultureInfo.InvariantCulture）。这解决了我的问题，因为时间格式的点是它的根本原因。
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.
但是ToString（yyyy-MM-dd HH：mm：ss）会根据操作系统在相同的文化中返回不同的格式吗？ ：O这太可怕了。
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.
这篇关于MSSQL查询不再适用于Windows 10客户端：将varchar数据类型转换为datetime数据类型导致超出范围值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！