由于语言环境，将字符串转换为datetime时出错 [英] Error converting string to datetime due to locale

问题描述

我在英国遇到了以下情况：

  SELECT CAST（'2012-12-31'as DATETIME）
  pre> 
 
 
错误信息：
 
 
将varchar数据类型转换为datetime数据类型导致超出范围的值。
 
 
 Windows服务器语言环境是英国英语。我的登录区域是英国英语。数据库'language'是英文（美国），但是这与其他服务器上的另一个实例是一样的。以上SQL不会失败。
 
 
 
任何想法？
解决方案
用户进行数据库连接 -  SQL用户 - 将语言设置为英文。
 
 
 
这是一个特定于发送查询的连接的SQL用户的设置
检查这是否是一个问题的一种方法...在Management Studio中运行此操作，并以发出查询的SQL用户身份登录
  SET LANGUAGE English 
 SELECT CAST（'2012-12-31'as DATETIME）
  
如果这样工作，正确设置SQL用户的默认语言
 
I'm having a lot of difficulty with locale's in a particular instance of SQL Server 2008 R2 Express.

I'm in the UK and the following fails:
SELECT CAST('2012-12-31' AS DATETIME)
Error message:

  
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The Windows server locale is British English.  My login locale is British English.  Collation 'if that matters' is Latin1_General_CI_AS.

The database 'language' is English (United States) but then this is the same as another instance on a different server and the above SQL doesnt fail.

Any thoughts?
解决方案
For the user making the database connection -- the SQL user -- set the language to English.

This is a setting specific to the SQL user of the connection issuing the query

One way to check if this is a problem... Run this in Management Studio and login as the SQL user who issues the query
SET LANGUAGE English
SELECT CAST('2012-12-31' AS DATETIME)
If this works, set the default language of the SQL user appropriately

                        
这篇关于由于语言环境，将字符串转换为datetime时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！