将 Varchar 列转换为日期时间格式 - SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22512480/
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
Convert Varchar Column to Datetime format - SQL Server
提问by Steve W
I have a table of data imported from CSV as follows:
我有一个从 CSV 导入的数据表，如下所示：
FirstTimeTaken LatestTimeTaken Market Outcome Odds NumberOfBets VolumeMatched InPlay
03/08/2013 15:30:14 03/08/2013 15:32:28 Over/Under 3.5 Goals Over 3.5 Goals 5 10 118 1
03/08/2013 14:26:40 03/08/2013 14:29:43 Correct Score 0 - 0 7 12 279 1
03/08/2013 15:15:34 03/08/2013 15:27:39 Match Odds Barnsley 110 7 9 1
28/07/2013 16:57:26 29/07/2013 21:35:55 Match Odds Barnsley 3 9 35 0
I had to import the first 2 columns in varchar format because I was getting errors trying to import as datetime. Now I have the data in a table, I need to convert the Column format from Varchar to Datetime. I tried:
我不得不以 varchar 格式导入前 2 列，因为我在尝试导入为日期时间时遇到错误。现在我有一个表中的数据，我需要将列格式从 Varchar 转换为日期时间。我试过：
ALTER TABLE #CSVTest_Data
ALTER COLUMN FirstTimeTaken DATETIME
ALTER TABLE #CSVTest_Data
ALTER COLUMN LatestTimeTaken DATETIME
This results in error: 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value'. I know that removing the last row of data gets rid of the problem, so I suspect that the system thinks the date format is MM/DD/YYYY whereas it is actually DD/MM/YYYY. The following query works fine:
这会导致错误：“将 varchar 数据类型转换为日期时间数据类型导致值超出范围”。我知道删除最后一行数据可以解决问题，所以我怀疑系统认为日期格式是 MM/DD/YYYY 而它实际上是 DD/MM/YYYY。以下查询工作正常：
SELECT convert(VARCHAR(50),FirstTimeTaken,105) from #CSVTest_Data
SELECT convert(VARCHAR(50),LatestTimeTaken,105) from #CSVTest_Data
But this does not convert the column format to datetime. I would appreciate some help on how to do this. Thanks.
但这不会将列格式转换为日期时间。我将不胜感激有关如何执行此操作的一些帮助。谢谢。
采纳答案by The Jonas Persson
Try using SET DATEFORMAT.
尝试使用SET DATEFORMAT.
SET DATEFORMAT dmy
回答by Dave Mason
You can select the data from your #Temp table as follows:
您可以从 #Temp 表中选择数据，如下所示：
SELECT
CONVERT(DATETIME, FirstTimeTaken, 103 ),
CONVERT(DATETIME, LatestTimeTaken, 103)
FROM #CSVTest_Data
This returns the fields as DATETIME data types. From there, do whatever you need to.
这将字段返回为 DATETIME 数据类型。从那里开始，做任何你需要做的事情。
