SQL - varchar 数据类型到日期时间数据类型的转换导致值超出范围

2021-12-10 00:00:00 datetime tsql sql-server

我在运行 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.

有关转换格式和进一步阅读的信息,请参阅此链接.https://www.w3schools.com/sql/func_sqlserver_convert.asp

Refer this link for conversion formats and further reading. https://www.w3schools.com/sql/func_sqlserver_convert.asp

相关文章