在 SQL Server 中将 varchar 转换为日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1509977/
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 into datetime in SQL Server
提问by Developer
How do I convert a string of format mmddyyyyinto datetimein SQL Server 2008?
如何将格式字符串转换mmddyyyy为datetimeSQL Server 2008 中的格式？
My target column is in DateTime
我的目标列在 DateTime
I have tried with Convertand most of the Datestyle values however I get an error message:
我已经尝试过Convert大多数Date样式值，但是我收到一条错误消息：
'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'
“将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。”
回答by KM.
OP wants mmddyy and a plain convert will not work for that:
OP 需要 mmddyy，而简单的转换将不起作用：
select convert(datetime,'12312009')
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value
so try this:
所以试试这个：
DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))
OUTPUT:
输出：
-----------------------
2009-12-31 00:00:00.000
(1 row(s) affected)
回答by nick
SQL Server can implicitly cast strings in the form of 'YYYYMMDD' to a datetime - all other strings must be explicitly cast. here are two quick code blocks which will do the conversion from the form you are talking about:
SQL Server 可以将 'YYYYMMDD' 形式的字符串隐式转换为日期时间 - 所有其他字符串必须显式转换。这是两个快速代码块，它们将从您正在谈论的表单进行转换：
version 1 uses unit variables:
版本 1 使用单位变量：
BEGIN
DECLARE @input VARCHAR(8), @mon CHAR(2),
@day char(2), @year char(4), @output DATETIME
SET @input = '10022009' --today's date
SELECT @mon = LEFT(@input, 2), @day = SUBSTRING(@input, 3,2), @year = RIGHT(@input,4)
SELECT @output = @year+@mon+@day
SELECT @output
END
version 2 does not use unit variables:
版本 2 不使用单位变量：
BEGIN
DECLARE @input CHAR(8), @output DATETIME
SET @input = '10022009' --today's date
SELECT @output = RIGHT(@input,4) + SUBSTRING(@input, 3,2) + LEFT(@input, 2)
SELECT @output
END
Both cases rely on sql server's ability to do that implicit conversion.
这两种情况都依赖于 sql server 进行隐式转换的能力。
回答by Stephen Himes
I found this helpful for my conversion, without string manipulation. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
我发现这对我的转换很有帮助，无需进行字符串操作。https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
CONVERT(VARCHAR(23), @lastUploadEndDate, 121)
yyyy-mm-dd hh:mi:ss.mmm(24h) was the format I needed.
yyyy-mm-dd hh:mi:ss.mmm(24h) 是我需要的格式。
回答by HLGEM
Likely you have bad data that cannot convert. Dates should never be stored in varchar becasue it will allow dates such as ASAP or 02/30/2009. Use the isdate() function on your data to find the records which can't convert.
您可能有无法转换的错误数据。日期不应存储在 varchar 中，因为它允许诸如 ASAP 或 02/30/2009 之类的日期。对您的数据使用 isdate() 函数来查找无法转换的记录。
OK I tested with known good data and still got the message. You need to convert to a different format becasue it does not know if 12302009 is mmddyyyy or ddmmyyyy. The format of yyyymmdd is not ambiguous and SQL Server will convert it correctly
好的，我使用已知的良好数据进行了测试，但仍然收到消息。您需要转换为不同的格式，因为它不知道 12302009 是 mmddyyyy 还是 ddmmyyyy。yyyymmdd 的格式没有歧义，SQL Server 会正确转换
I got this to work:
我得到了这个工作：
cast( right(@date,4) + left(@date,4) as datetime)
You will still get an error message though if you have any that are in a non-standard format like '112009' or some text value or a true out of range date.
但是，如果您有任何非标准格式（如“112009”或某些文本值或真正超出范围的日期），您仍会收到错误消息。
回答by Andrew
Convert would be the normal answer, but the format is not a recognised format for the converter, mm/dd/yyyy could be converted using convert(datetime,yourdatestring,101) but you do not have that format so it fails.
Convert 将是正常的答案，但该格式不是转换器可识别的格式，mm/dd/yyyy 可以使用 convert(datetime,yourdatestring,101) 进行转换，但您没有该格式，因此它失败了。
The problem is the format being non-standard, you will have to manipulate it to a standard the convert can understand from those available.
问题是格式是非标准的，您必须将其操作为转换可以从可用格式中理解的标准。
Hacked together, if you can guarentee the format
一起破解，如果你能保证格式
declare @date char(8)
set @date = '12312009'
select convert(datetime, substring(@date,5,4) + substring(@date,1,2) + substring(@date,3,2),112)
回答by JonH
Look at CAST/ CONVERTin BOL that should be a start.
在 BOL 中查看CAST/CONVERT应该是一个开始。
If your target column is datetimeyou don't need to convert it, SQL will do it for you.
如果您的目标列是datetime您不需要转换它，SQL 会为您做。
Otherwise
除此以外
CONVERT(datetime, '20090101')
Should do it.
应该做。
This is a linkthat should help as well:
这是一个也应该有帮助的链接：
回答by Shnugo
I'd use STUFFto insert dividing chars and then use CONVERTwith the appropriate style. Something like this:
我会STUFF用来插入分隔字符，然后使用CONVERT适当的样式。像这样的东西：
DECLARE @dt VARCHAR(100)='111290';
SELECT CONVERT(DATETIME,STUFF(STUFF(@dt,3,0,'/'),6,0,'/'),3)
First you use two times STUFFto get 11/12/90 instead of 111290, than you use the 3to convert this to datetime(or any other fitting format: use .for german, -for british...) More details on CAST and CONVERT
首先，您使用两次STUFF获得 11/12/90 而不是 111290，然后使用3将其转换为datetime（或任何其他合适的格式：.用于德语，-用于英国...）更多详细信息CAST and CONVERT
Best was, to store date and time values properly.
最好的是，正确存储日期和时间值。
- This should be either "universal unseparated format"
yyyyMMdd - or (especially within XML) it should be ISO8601:
yyyy-MM-ddoryyyy-MM-ddThh:mm:ssMore details on ISO8601
- 这应该是“通用未分隔格式”
yyyyMMdd - 或（尤其是在 XML 中）它应该是 ISO8601：
yyyy-MM-dd或yyyy-MM-ddThh:mm:ss有关 ISO8601 的更多详细信息
Any culture specific format will lead into troubles sooner or later...
任何特定于文化的格式迟早都会导致麻烦......
回答by Shahab J
use Try_Convert:Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
use Try_Convert：如果转换成功，则返回转换为指定数据类型的值；否则，返回 null。
DECLARE @DateString VARCHAR(10) ='20160805'
SELECT TRY_CONVERT(DATETIME,@DateString)
SET @DateString ='Invalid Date'
SELECT TRY_CONVERT(DATETIME,@DateString)
回答by Ryan
I had luck with something similar:
我很幸运有类似的事情：
Convert(DATETIME, CONVERT(VARCHAR(2), @Month) + '/' + CONVERT(VARCHAR(2), @Day)
+ '/' + CONVERT(VARCHAR(4), @Year))
回答by Denis Maslov
The root cause of this issue can be in the regional settings - DB waiting for YYYY-MM-DD while an app sents, for example, DD-MM-YYYY (Russian locale format) as it was in my case. All I did - change locale format from Russian to English (United States) and voilà.
此问题的根本原因可能在于区域设置 - 数据库在应用程序发送时等待 YYYY-MM-DD，例如，DD-MM-YYYY（俄罗斯语言环境格式），就像我的情况一样。我所做的一切 - 将区域设置格式从俄语更改为英语（美国），瞧。
