将带有数据 MM/DD/YYYY varchar 的列转换为 sql server 中的日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22211282/
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 column with data MM/DD/YYYY varchar to date in sql server?
提问by TZ100
I've found some similar questions but haven't been able to get anything to work yet. I'm very much a novice with little SQL experience.
我发现了一些类似的问题，但还没有得到任何工作。我是一个几乎没有 SQL 经验的新手。
I have a column END_DATE as Varchar(10) where all the rows follow the mm/dd/yyyy format and I would like to convert it to date. I have an empty column formatted as date if that helps. There are 36 million rows.
我有一列 END_DATE 作为 Varchar(10)，其中所有行都遵循 mm/dd/yyyy 格式，我想将其转换为日期。如果有帮助，我有一个格式化为日期的空列。有 3600 万行。
回答by Nick.McDermaid
SELECT CONVERT(DATETIME,YourColumn,101) FROM YourTable
101 is mm/dd/yyyy format.
101 是 mm/dd/yyyy 格式。
You zany backwards americans :)
你这可笑的倒退美国人:)
To update your existing column
更新现有列
UPDATE YourTable
SET YourNewColumn = CONVERT(DATETIME,YourOldColumn,101)
Since it appears you have invalid data, use this method to isolate it:
由于看起来您有无效数据，请使用此方法将其隔离：
UPDATE YourTable
SET YourNewColumn = CONVERT(DATETIME,YourOldColumn,101)
WHERE SomeTableKey BETWEEN ASmallCode AND ABiggerCode
Find a key in your table that you can use to divide up the data and try updating half the table... now halve it again and again until you find the offending data. Post the data here and we will come up with some code to allow for it.
在您的表中找到一个键，您可以使用它来划分数据并尝试更新一半的表……现在一次又一次地将其减半，直到找到有问题的数据。在这里发布数据，我们将提供一些代码来允许它。
回答by user3193257
I think you should convert END_DATE to DATETIME type, because you have 36 million rows and it will give a performance boost when you do not have to cast or convert it datetime with select statement.
我认为您应该将 END_DATE 转换为 DATETIME 类型，因为您有 3600 万行，并且当您不必使用 select 语句强制转换或转换日期时间时，它会提高性能。
To answer your question, you can do something like
要回答您的问题，您可以执行以下操作
select CAST(END_DATE AS DATETIME) FROM MyTable
从 MyTable 中选择 CAST(END_DATE AS DATETIME)
回答by M.Ali
DECLARE @End_DATE VARCHAR(10);
SET @End_DATE = '12/20/2013';
SELECT CAST(@End_DATE AS DATE)
RESULT: 2013-12-20
Empty Column formatted as Date, I am guessing you meant to say you have DATE datatype column and you would like to update this VARCHAR(10) date to , Date datatype column you could do something like this....
格式为日期的空列，我猜您的意思是说您有 DATE 数据类型列，并且您想将此 VARCHAR(10) 日期更新为日期数据类型列，您可以执行以下操作....
UPDATE Table_Name
SET New_End_DATE = CAST(End_DATE AS DATE)
Working SQL FIDDLE
Working SQL FIDDLE
回答by Stepan Riha
The PARSE(SQL 2012) or CONVERT(any version) can do the conversion for you. Your UPDATE query will look something like one of these statements:
该PARSE（SQL 2012）或CONVERT（任何版本），可以为你做转换。您的 UPDATE 查询将类似于以下语句之一：
UPDATE the_table SET the_column = PARSE(end_date AS datetime2 USING 'en-US')
UPDATE the_table SET the_column = CONVERT(datetime2, end_date, 101)
