SQL 错误：将 varchar 数据类型转换为 datetime 数据类型导致值超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6701338/
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
SQL Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
提问by CodeMinion
The data going in:
进入的数据：
<F1>000001234</F1>
<F2>133228579</F2>
<F3>2011-05-25</F3>
<F4>21-332211</F4>
<F5>TxtMail</F5>
<F6/>
<F7>26/04/2011</F7>
<F8>00:09:13</F8>
<F9>0:00</F9>
<F10/>
<F11/>
<F12>Text Service</F12>
<F13>0294443333</F13>
<F14>TXT</F14>
<F15>FR</F15>
<F16>0.17</F16>
Relevant parts of the stored procedure :
存储过程的相关部分：
@F1 VARCHAR(24) = NULL, --AccountNumber
@F2 VARCHAR(24) = NULL, --InvoiceNumber
@F3 VARCHAR(24) = NULL, --InvoiceDate
@F4 VARCHAR(24) = NULL, --CallerNumber
@F5 VARCHAR(10) = NULL, --Service
@F6 VARCHAR(10) = NULL, --
@F7 varchar(24) = NULL, --CallDate
@F8 VARCHAR(24) = NULL, --CallTime
@F9 VARCHAR(50) = NULL, --Duration
@F10 VARCHAR(50) = NULL, --
@F11 VARCHAR(10) = NULL, --
@F12 VARCHAR(24) = NULL, --Network
@F13 VARCHAR(24) = NULL, --CallingNumber
@F14 VARCHAR(10) = NULL, --Type
@F15 VARCHAR(10) = NULL, --TypeName
@F16 MONEY = NULL, --Amount
DECLARE @Date VARCHAR(20)
SET @Date = RIGHT(@F7,4)+'/'+SUBSTRING(@F7,4,2)+'/'+LEFT(@F7,2)
-- Combine the date and time into a datetime data type
-- For Time
DECLARE @time DATETIME
SET @time = CONVERT(DATETIME, @Date + ' ' + @F8)
The error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
错误：将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。
This is the only invocation of CONVERT(DATETIME) and I don't use CAST
这是 CONVERT(DATETIME) 的唯一调用，我不使用 CAST
If I just pass in the data directly, the row gets inserted. If I run the sproc, it goes out-of-range.
如果我直接传入数据，则会插入该行。如果我运行 sproc，它就会超出范围。
回答by Mikael Eriksson
The safe datetime string formats to use in SQL Server is
在 SQL Server 中使用的安全日期时间字符串格式是
YYYYMMDD HH:MM:SSor YYYY-MM-DDTHH:MM:SS.
YYYYMMDD HH:MM:SS或YYYY-MM-DDTHH:MM:SS。
The conversion you have will fail if SET DATEFORMATis dmy. SET LANGUAGEwill automatically set date format for you. I think both germanand norwegianuse dmy.
如果SET DATEFORMAT为 dmy，您的转换将失败。SET LANGUAGE会自动为您设置日期格式。我认为无论是german和norwegian使用DMY。
This will fail:
这将失败：
set language norwegian
declare @F7 varchar(10) = '26/04/2011'
declare @F8 varchar(10) = '00:09:13'
DECLARE @Date VARCHAR(20)
SET @Date = RIGHT(@F7,4)+'/'+SUBSTRING(@F7,4,2)+'/'+LEFT(@F7,2)
DECLARE @time DATETIME
SET @time = CONVERT(DATETIME, @Date + ' ' + @F8)
Do like this instead (using YYYY-MM-DDTHH:MM:SS) to be safe regardless of language/dateformat settings.
无论语言/日期格式设置如何，都这样做（使用 YYYY-MM-DDTHH:MM:SS）以确保安全。
declare @F7 varchar(10) = '26/04/2011'
declare @F8 varchar(10) = '00:09:13'
DECLARE @Date VARCHAR(20)
SET @Date = RIGHT(@F7,4)+'-'+SUBSTRING(@F7,4,2)+'-'+LEFT(@F7,2)
DECLARE @time DATETIME
SET @time = CONVERT(DATETIME, @Date + 'T' + @F8)
回答by sudheshna
Try the following and see what you are getting,
尝试以下操作，看看你得到了什么，
DECLARE @Date VARCHAR(20)
SET @Date = RIGHT('26/04/2011',4)+'/'+SUBSTRING('26/04/2011',4,2)+'/'+LEFT('26/04/2011',2)
-- Combine the date and time into a datetime data type
-- For Time
DECLARE @time DATETIME
SET @time = CONVERT(DATETIME, @Date + ' ' + '00:09:13')
print @time
