SQL 将 varchar MM/DD/YYYY 数据转换或转换为日期时间 MM/DD/YYYY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15578501/
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 or cast varchar MM/DD/YYYY data into datetime MM/DD/YYYY
提问by Sam
I'm trying to convert/cast varchar mm/dd/yyyy data into datetime mm/dd/yyyy format so that I can query a range using a between clause.
我正在尝试将 varchar mm/dd/yyyy 数据转换/转换为日期时间 mm/dd/yyyy 格式，以便我可以使用 between 子句查询范围。
I've seen a similar question asked previously, but without the slashes in the date and I'm not sure how to work around that.
我之前看到过一个类似的问题，但日期中没有斜线，我不知道如何解决这个问题。
回答by Aaron Bertrand
To do a proper range query, you can convert from your bad regional formats in a predictable way, and then use an open-ended range. Now this doesn't protect you from someone entering 06/08/2013and meaning August 6th instead of June 8th, but if you support free text entry, that's a risk you'll have to take.
要进行适当的范围查询，您可以以可预测的方式从错误的区域格式进行转换，然后使用开放式范围。现在这并不能保护您免于有人进入06/08/2013并意味着 8 月 6 日而不是 6 月 8 日，但是如果您支持自由文本输入，那么您将不得不承担风险。
DECLARE @s DATETIME, @e DATETIME;
SELECT @s = CONVERT(DATETIME, '03/13/2013', 101),
@e = CONVERT(DATETIME, '03/26/2013', 101);
SELECT <cols>
FROM dbo.table_name
WHERE datetime_column >= @s
AND datetime_column < DATEADD(DAY, 1, @e);
Ideally, you wouldn't allow users to type in regional nonsense like m/d/y. If you control the input format to the stored procedure, you can send an unambiguous date literal like YYYYMMDD. Or better yet, use a strongly typed parameter. The more you can take away from users entering ad hoc nonsense the less headache you'll have. Please read these articles:
理想情况下，您不会允许用户输入诸如m/d/y. 如果您控制存储过程的输入格式，您可以发送一个明确的日期文字，如YYYYMMDD. 或者更好的是，使用强类型参数。您可以从输入临时废话的用户那里获得的越多，您的头痛就越少。请阅读这些文章：
