SQL - 将varchar数据类型转换为datetime数据类型导致超出范围值 [英] SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
问题描述
在运行SQL时,我将数据类型值从 varchar
转换为 datetime
I have been getting the following error when running a SQL to convert my data type value from varchar
to datetime
.
消息242,级别16,状态3,行1
将varchar数据类型转换为datetime数据类型导致超出范围的值。
Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
我已经检查了数据,看不到任何奇怪的事情:支票和所有返回的结果
I have checked the data and can't see anything to odd: Ran the following checks and all returning no results
SELECT [Date] from table where [DATe] is null
SELECT [Date] from table where [DATe] = ''
SELECT [Date] from table where LEN([date])> 10
SELECT [Date] from table where LEN([date])< 10
SELECT top 100 [Date] , SUBSTRING([date],4,2) from [table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 12
SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 31
还有什么值得关注的,也许值得任何指针或帮助这个问题?似乎没有得到它的底线。
Is there anything else worth looking at and maybe worth any pointers or help with this issue? Can't seem to get bottom of it.
推荐答案
我一周前面临同样的问题。
问题是时区设置。以其他格式指定,如mm / dd / yyyy(通常工作)。
I have faced the same problem a week ago. The problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).
将日期指定为 30/12/2013 导致我的错误。但是,将其指定为mm / dd / yyyy格式。
Specifying the date as 30/12/2013 resulted in the error for me. However, specifying it as mm/dd/yyyy format worked.
如果您需要转换输入,您可以尝试查看 CONVERT
方法。
语法是
If you need to convert your input the you can try looking into the CONVERT
method.
Syntax is
CONVERT(VARCHAR,@ your_date_Value,103)
CONVERT(VARCHAR,'12 / 30/2013',103)
完成 103 是日期时间格式。
The finishing 103 is the datetime format.
请参阅此链接以获取转换格式并进一步阅读。
http://www.w3schools.com/sql/func_convert.asp
Refer this link for conversion formats and further reading. http://www.w3schools.com/sql/func_convert.asp
这篇关于SQL - 将varchar数据类型转换为datetime数据类型导致超出范围值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!