0

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?

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
Håkon Seljåsen
  • 589
  • 5
  • 18
  • Different dateformat `SET DATEFORMAT ` – Lukasz Szozda Aug 31 '15 at 11:06
  • 1
    Are you sure that English Version Windows have the same locale not for example `US` vs `EN` and date `yyyy/dd/mm` vs `yyyy/mm/dd`? – Lukasz Szozda Aug 31 '15 at 11:12
  • I had not checked for US - EN, but changing this did not alter the behavior. Researching your first comment, Lad2025, I noticed that it is the time part of the DateTime which is causing problems. In our code we format this by a ToString("yyyy-MM-dd HH:mm:ss") cast. Which for some reason has been changed to "yyyy-MM-dd HH.mm.ss" on the windows 10 machine. I will try out @JonSkeet's answer in the following post as a solution tomorow and post an update afterwords: http://stackoverflow.com/questions/18874102/net-datetime-tostringmm-dd-yyyy-hhmmss-fff-resulted-in-something-like – Håkon Seljåsen Aug 31 '15 at 14:50

1 Answers1

2

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.

Community
  • 1
  • 1
Håkon Seljåsen
  • 589
  • 5
  • 18