SQL 将英国格式日期转换为日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15275813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting UK format date to datetime
提问by user2145047
I have a value in SQL that represents a date, but it of type nvarchar. The value of the date is in the format:
我在 SQL 中有一个表示日期的值，但它的类型为 nvarchar。日期的值采用以下格式：
dd/mm/yyyy hh:mm
I need to present this column via a view to a CCure 800 database in DATETIMEformat. I expected to use CAST/CONVERT; however, when using this, the following error is returned:
我需要通过视图以DATETIME格式显示此列到 CCure 800 数据库。我希望使用CAST/ CONVERT; 但是，使用它时，会返回以下错误：
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
消息 242，级别 16，状态 3，第 1 行
varchar 数据类型到 datetime 数据类型的转换导致值超出范围。
回答by Aaron Bertrand
Please stop storing dates as strings, and especiallyas Unicode strings. Are you concerned that some future version of our calendar will have umlauts, pound signs, hieroglyphics, or Mandarin instead of numbers?
请停止将日期存储为字符串，尤其是 Unicode 字符串。您是否担心我们日历的某些未来版本将使用变音符号、井号、象形文字或普通话而不是数字？
Store dates as dates. That is what those data types are for. In addition to not having to worry about invalid interpretations, you also get all of the benefits of things like DATEPARTand DATEADD, and you don't have to worry about anyone stuffing nonsense into the column (anything from 31/02/2012to 'I don''t want to enter a real date'...
将日期存储为日期。这就是这些数据类型的用途。除了不必对无效诠释担心，你还可以获得所有事物的好处喜欢DATEPART和DATEADD，你不必对任何人废话馅入列（从任何担心31/02/2012到'I don''t want to enter a real date'...
In the meantime, you just need to use a style number with CONVERT(this won't work reliably with CAST):
同时，您只需要使用样式编号CONVERT（这将无法可靠地使用CAST）：
SELECT CONVERT(DATETIME, '13/06/2013 09:32', 103);
To make it work with CAST, you could set your LANGUAGEor DATEFORMATsettings accordingly, but you can't do this inside a view, and it makes the code very brittle anyhow IMHO.
为了使它与 一起工作CAST，您可以相应地设置您的LANGUAGE或DATEFORMAT设置，但是您不能在视图中执行此操作，并且无论如何恕我直言，它会使代码变得非常脆弱。
SET DATEFORMAT DMY;
SELECT CAST('13/06/2013 09:32' AS DATETIME);
Or
或者
SET LANGUAGE BRITISH;
SELECT CAST('13/06/2013 09:32' AS DATETIME);
By a very wide margin, I prefer the additional control CONVERTgives you, and almost unilaterally change all instances of CASTto CONVERT, even when this control is not needed, for consistency (why use CASTin some cases, and CONVERTwhen you need it, when you can just use CONVERTalways?).
在很大程度上，我更喜欢额外的控制CONVERT给你，并且几乎单方面地改变所有实例CASTto CONVERT，即使不需要这个控制，为了一致性（为什么CAST在某些情况下使用CONVERT它，当你需要它时，当你可以CONVERT总是使用？）。
EDIT
编辑
To identify the garbage data that has snuck into your table because of a bad data type choice, do this (and then fix the data or, better yet, fix the data type so this doesn't happen again):
要识别由于错误的数据类型选择而潜入表中的垃圾数据，请执行以下操作（然后修复数据，或者更好的是修复数据类型，以免再次发生）：
SET DATEFORMAT DMY;
SELECT date_column FROM dbo.table_name WHERE ISDATE(date_column) = 0;
