什么可能导致“将char数据类型转换为日期时间数据类型导致超出范围”?

时间:2021-09-03 16:44:38

I am running into an error I am having trouble figuring out.

我遇到了一个错误,我无法搞清楚。

I have 2 tables and I'm trying to copy data from one to the other (simplified view):

我有2个表,我正在尝试将数据从一个复制到另一个(简化视图):

MyTable
-------
ID varchar(11) do not allow nulls
Field01 numeric(6,0) allow nulls

MyTable_Temp
------------
ID varchar(11) do not allow nulls
Field01 numeric(6,0) allow nulls

My query looks like this:

我的查询如下所示:

DELETE FROM dbo.MyTable
INSERT INTO dbo.MyTable([ID],[Field01])
SELECT ID, Field01 FROM [dbo].MyTable_Temp WITH (NOLOCK)

However when I run my query it throws this error:

但是,当我运行我的查询时,它会抛出此错误:

Msg 242, Level 16, State 3, Procedure TRG_MyTable, Line 6 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

消息242,级别16,状态3,过程TRG_MyTable,第6行将char数据类型转换为日期时间数据类型导致超出范围的日期时间值。

If I comment out the Field01 part of the query it runs fine. How can a numeric field throw a datetime error?

如果我注释掉查询的Field01部分,它运行正常。数字字段如何抛出日期时间错误?

1 个解决方案

#1


It looks to me like you've got some kind of trigger on the destination table that's firing (TRG_MyTable is a giveaway) It's probably doing something like inserting a timestamped record into an audit table somewhere and is getting confused.

它看起来像你在目标表上有某种触发器正在触发(TRG_MyTable是赠品)它可能正在做一些事情,比如在某个地方将一个带时间戳的记录插入到审计表中并且让人感到困惑。

#1


It looks to me like you've got some kind of trigger on the destination table that's firing (TRG_MyTable is a giveaway) It's probably doing something like inserting a timestamped record into an audit table somewhere and is getting confused.

它看起来像你在目标表上有某种触发器正在触发(TRG_MyTable是赠品)它可能正在做一些事情,比如在某个地方将一个带时间戳的记录插入到审计表中并且让人感到困惑。