Convert.ToString(DateTime) 产生英国格式而不是美国格式
我遇到一个问题，C# DateTime 字符串无法转换为 SQL DateTime，因为它神秘地被格式化为英国日期 (dd/MM/yyyy)。以下是一系列事件：
- 在美国的远程服务器上创建一个对象并将其序列化为 xml。
- 该 xml 在 CA 的本地计算机上反序列化回对象。序列化日期如下所示： 2011-07-13T09:56:57.0542425
- 应用程序尝试调用前面提到的存储过程将对象保存到数据库中。它（不必要）将日期转换为字符串，然后使用 Convert.ToString(DateTime) 将其作为参数传递给存储过程。
- 存储过程失败并出现 SqlException“将数据类型 nvarchar 转换为 datetime 时出错”，因为它收到的 DateTime 类型参数的字符串采用 dd/MM/yyyy 格式（数据库语言为美国英语）。
现在，代码不应该将日期时间转换为字符串，然后再转换回 SQL 中的日期时间，但在一切正常一年多后，这个问题才开始发生（在多台计算机上）。所以我认为数据库或操作系统的文化最近一定发生了变化，导致它们使用不同的日期格式。令我惊讶的是，将操作系统（Windows 7）从英语（加拿大）更改为英语（美国）并重新启动后，问题仍然出现。更令人困惑的是，当本地创建相同类型的对象而不是反序列化时，无论区域设置如何，都不会发生错误。唯一的区别是序列化版本发生在 Windows 服务中，而本地创建的对象版本发生在 Windows 应用程序中。它们都使用自己的调用 Convert.ToString(DateTime) 的程序集副本，但它们使用该程序集的相同版本。我完全困惑了。
PS .NET 2.0 和SQL Server 2005
I'm having an issue where a C# DateTime string is failing to convert to a SQL DateTime because it is mysteriously being formatted as a UK date (dd/MM/yyyy). Here is the series of events:
- An object is created on a remote server in the US and serialized to xml.
- The xml is deserialized on a local computer in CA back to an object. The serialized date looks like this: 2011-07-13T09:56:57.0542425
- The application attempts to save the object to the database calling the previously mentioned stored procedure. It (needlessly) converts the date to a string before passing it as a parameter to the sproc using Convert.ToString(DateTime).
- The sproc fails with the SqlException "Error converting data type nvarchar to datetime" because the string it received for its DateTime typed parameter was in the dd/MM/yyyy format (and the database language is English US).
Now, the code shouldn't be converting the datetime to a string only to be converted back to a datetime in SQL, but this problem just started happening (on more than one computer) after everything was fine for over a year. So I thought the culture of the DB or the OS must have just recently changed causing them to use different date formats. To my surprise, after changing the OS (Windows 7) from English(Canada) to English(US) and restarting, the problem still occurs. To make it even more confusing, the error doesn't happen when when the same type of object is created locally instead of being deserialized, regardless of the regional settings. The only difference is the serialization version happens in a Windows service and the locally created object version happens in a Windows app. They both use their own copy of the assembly that calls Convert.ToString(DateTime) but they are using the same version of that assembly. I am utterly confused.
P.S. .NET 2.0 & SQL Server 2005
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
为什么不使用您特别想要的区域性来强制
DateTime.ToString()的格式，或者指定与您的 SQL 函数期望相匹配的自定义格式规则？对于自定义格式，您可以查看此处，或者对于特定于文化的格式，您可以请查看此处
Why do you not force the format of the
DateTime.ToString()using the culture you specifically want-- or specify a custom formatting rule that matches what your SQL functions expect?For custom formatting you can look here or for culture specific formatting, you can look here
该服务是否可能在区域设置错误的帐户下运行？
如果是这样，那么也许 Microsoft 的这些说明可能适用。
Is it possible that the service is running under an account which has the wrong regional settings?
If so, then perhaps these instructions from Microsoft might apply.
我不相信
DateTime中除了简单的 64 位整数之外还有任何东西 - 基本上它是以刻度为单位的日期/时间，以及在前几位中编码的“种类”。因此，创建一个新的DateTime是在本地创建的。换句话说，恐怕我怀疑你关于“在本地创建相同类型的对象”时会发生什么的说法。这就是我开始调查的地方 - 摆脱数据库调用，但只记录调用
Convert.ToString(serializedDateTime)和Convert.ToString(new DateTime(2011, 7, 25))（作为日期的示例，其中字符串表示形式使事物的方向变得明显）。如果您可以得到两种不同的日期格式，我会感到非常惊讶 - 一种用于已反序列化的值，另一种用于本地创建的值。反序列化后得到什么“种类”的
DateTime（即获取Kind属性的结果）？有了这些信息，您应该能够构造一个完全等于DateTime值。I don't believe there is anything in a
DateTimebeyond a simple 64-bit integer - basically it's the date/time in ticks, and the "kind" encoded in the top couple of bits. So creating a newDateTimeis created locally. In other words, I'm afraid I doubt your claim about what happens when "the same type of object is created locally".That's where I'd start investigating - get rid of the database call, but just log the result of calling
Convert.ToString(serializedDateTime)andConvert.ToString(new DateTime(2011, 7, 25))(as an example of a date where the string representation makes it obvious which way round things are). I'd be really surprised if you can get that to give two different date formats - one for the value which had been deserialized and one for the value which was created locally.What "kind" of
DateTimedo you get after deserialization (i.e. the result of fetching theKindproperty)? With that information you should be able to construct an exactly equalDateTimevalue.