在 SQL Server 中将 varchar 转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41657285/
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 from varchar into date in SQL Server
提问by moe
This looks easy solution but I can't seem to figure out as to why this is not working for me. I have a column that has data like this:
这看起来很简单的解决方案，但我似乎无法弄清楚为什么这对我不起作用。我有一列包含这样的数据：
DateField
----------
12/16/2016
11/06/2016
All I want to do is to convert from varcharinto a datecolumn, but I am getting this error:
我想要做的就是将 from 转换varchar为一date列，但出现此错误：
Conversion failed when converting date and/or time from character string.
从字符串转换日期和/或时间时转换失败。
Here is my simple query:
这是我的简单查询：
select convert (date, DateField) as convertedField
from myTable
回答by P?????
Nothing wrong with the two examples you have given. There are some bad dates in your table which cannot be converted to date.
你给出的两个例子没有任何问题。您的表中有一些无法转换为日期的错误日期。
Use TRY_CONVERTfunction for bad dates it will return NULL
TRY_CONVERT对错误日期使用函数，它将返回NULL
select TRY_Convert(date,DateField)
From myTable
You should always store dates in DATE/DATETIMEdatatype.
您应该始终以DATE/DATETIME数据类型存储日期。
If you want to see the records which cannot be converted to date then
如果您想查看无法转换为日期的记录，则
select DateField
From myTable
Where TRY_Convert(date,DateField) IS NULL
回答by Hadi
If working with a specific date format like mm/dd/yyyyYou can specify it in Convert()function like the following
如果使用特定的日期格式，例如mm/dd/yyyy您可以Convert()在如下所示的函数中指定它
CONVERT(DATETIME,DATAFIELD,101)
If it still is not working, use TRY_CONVERT()to get which rows are throwing this exception:
如果它仍然不起作用，请使用TRY_CONVERT()获取哪些行抛出此异常：
SELECT *
FROM TBL
WHERE TRY_CONVERT(DATETIME, DATAFIELD, 101) IS NULL
This will return rows that cannot be converted
这将返回无法转换的行
TRY_CONVERT()will return NULLif conversion failed
TRY_CONVERT()NULL如果转换失败将返回
Read more about DateTimeformats here: SQL Server CONVERT() Function tutorial
在此处阅读有关DateTime格式的更多信息： SQL Server CONVERT() 函数教程
回答by Aatish Sai
You need to specify the format of date time while formatting. The date in your table is currently in U.S format so you should pass the third argument 101 in your convert function.
格式化时需要指定日期时间的格式。表中的日期当前采用美国格式，因此您应该在转换函数中传递第三个参数 101。
SELECT CONVERT(date,[DateField],101) FROM myTable;
Working Fiddle here http://rextester.com/NYKR49788
在这里工作小提琴http://rextester.com/NYKR49788
More info about date time style here: https://msdn.microsoft.com/en-us/library/ms187928.aspx
有关日期时间样式的更多信息：https: //msdn.microsoft.com/en-us/library/ms187928.aspx
