SQL - 将varchar数据类型转换为datetime数据类型时导致值超出范围

Question

SQL - 将varchar数据类型转换为datetime数据类型时导致值超出范围

127

当我运行一个SQL将我的数据类型值从varchar转换为datetime时，我一直遇到以下错误:

Msg 242，级别16，状态3，第1行将varchar数据类型转换为datetime数据类型导致超出范围的值。

我已经检查了数据，但没有发现任何问题：运行以下检查并全部未返回结果

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

还有什么其他值得关注的地方，也许需要一些建议或帮助解决这个问题？似乎无法找到问题的根源。

- user23495

3

日期列的数据类型是什么？您可以向我展示表格结构以及出现错误的语句吗？ - Spikeh

3

你提供的六个SQL语句中，哪一个失败了？ - Mureinik

1

这六个语句都能正常工作，并验证了数据没有问题。 - user23495

3

您还没有检查不合法的日期，例如2013-10-31或2013-02-30。可能，您遇到的错误与这种有问题的日期有关。 - Dalen

2

嗨，Dalen，我已经完成了这个检查。数据设置的方式是31/10/2013、30/10/2013。它是以英国格式设置的。当尝试更改列类型时，这会产生任何影响吗？我认为不会。 - user23495

显示剩余5条评论

20个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- gordon · Answer 1

测试年份是否大于2079。我发现一个用户在年份（10/12/2106）中打错了，应该是2016而不是2106，结果出现错误；因此在2016年10月12日进行测试，发现SQL Server接受到2078年，如果年份为2079或更高，则会抛出错误。我没有进一步研究SQL Server的日期滑动方式。

- user5714464 · Answer 2

我也在向一列自动插入系统日期时遇到了这个问题。

我的解决方法是将我的系统日期格式更改为与SQL服务器的日期格式匹配，例如：我的SQL格式为mm/dd/yyyy，而我的系统格式设置为dd/mm/yyyy。我将我的系统格式更改为mm/dd/yyyy，错误消失了。

-kb

- Andre · Answer 3

我只需将要转换的varchar字段转换为一个新表（带有DateTime字段）中的DateTime兼容布局，然后SQL将无问题地从varchar转换为DateTime。

在下面的（不是我创建的具有这些名称的表！）中，如果您想要，我只需使varchar字段成为类似DateTime的形式：

update report1455062507424 
set [Move Time] = substring([Move Time], 7, 4) + '-'+ substring([Move Time], 4, 2) + '-'+ substring([Move Time], 1, 2) + ' ' + 
    substring([Move Time], 12, 5)

- Talha Imam · Answer 4

我遇到了这个错误，因为我尝试使用C#代码直接从内联查询将最小日期和时间存储在列中。

代码中的日期变量设置为01/01/0001 12:00:00 AM，因为如果没有其他设置，C#中的DateTime会使用此日期和时间进行初始化。而且，MS-SQL 2008 datetime数据类型允许的最早日期是1753-01-01 12:00:00 AM。

我将代码中的日期更改为01/01/1900，之后就没有报告任何错误了。

- Khalid · Answer 5

Varchar Date Convert to Date and Change the Format

2016年11月12日 12:00，2016年12月21日，2016年12月21日此查询适用于将上述内容更改为此格式dd/MM/yyyy SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

- L0uis · Answer 6

我在日期上使用了ToString()，但是使用的是mm而不是MM。

- Kevin Stephen Biswas · Answer 7

我曾经遇到过类似的问题。我认为不会有太多人面临我所面临的情况。尽管如此，我仍然想分享我的经验。我在测试环境中工作，其中有日期时间数据设置为最小日期时间值0001/01/01。这个值小于smalldatetime数据类型的最小可能数据。因此，请确保检查数据类型的最小和最大值。对于smalldatetime，您可以参考此页面：https://learn.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15

- Jessica Williams · Answer 8

您遇到的错误消息表明将varchar数据类型转换为datetime数据类型时出现问题，导致超出范围的值。看起来您已经执行了几个检查以确定数据是否存在潜在问题，例如检查null值、空字符串、长度和各个日期组件。

以下是一些建议用于进一步调查和解决此问题：

请检查日期格式：验证 varchar 列中的日期值是否符合预期格式。该格式应与 SQL Server 的区域设置所期望的格式相匹配。例如，如果日期格式为"MM/dd/yyyy"，请确保数据与此格式匹配。

处理日期分隔符：确认 varchar 列中的日期分隔符（如"/"、"-"、"."）是否与预期格式相匹配。如果分隔符不同或不一致，可能会导致转换错误。您可能需要执行数据清洗或使用字符串操作函数来调整日期值以进行转换。

识别特定问题值：如果可能，请尝试确定引发错误的具体值。您可以逐渐缩小数据集并关注触发转换错误的特定行或值，以缩小搜索范围。

处理潜在的区域差异：请注意日期格式中的区域差异，如日、月、年的顺序。确保您的 SQL Server 设置与期望的格式匹配。

您可以利用dbForge Studio的 SQL 编辑和调试功能来查看和修改查询语句。

- tonderaimuchada · Answer 9

请确保您的日期与数据库管理器（例如SQL Server Management Studio）兼容或可以正常运行。例如，DateTime.Now C#函数在SQL Server中无效，这意味着您的查询必须包括有效的函数，如SQL Server的GETDATE()。

这个更改对我非常有效。

- wobblycogs · Answer 10

这个问题的原因有点不寻常，但以防万一有人需要。我正在处理的代码使用了：

java.text.DateFormat.getDateTimeInstance()

获取日期格式化程序。通过此调用返回的格式化模式从Java 8到Java 9发生了更改，如此错误报告中所述：https://bugs.openjdk.java.net/browse/JDK-8152154 显然，它返回给我的格式不适合数据库。解决方案是使用以下内容：

DateTimeFormatter.ISO_LOCAL_DATE_TIME