SQL Server：从2008年到2014年： - 数据类型的日期时间和时间在add运算符中不兼容。还有其他方法吗？

时间：2021-06-10 17:03:28

I am migrating from SQL Server 2008 To 2014 and I get an error

我正在从SQL Server 2008迁移到2014年,我收到一个错误

The data types datetime and time are incompatible in the add operator.

数据类型datetime和time在add运算符中不兼容。

I figured out the solution convert time to DateTime, but I have changes in many stored procedures and views.

我想出了解决方案将时间转换为DateTime,但我在许多存储过程和视图中都有变化。

Is there any other solution for above problem?

针对上述问题还有其他解决方案吗?

1 个解决方案

#1

There is no other solution than re-factoring your code if you wish to upgrade to SQL2014. The example below demonstrates that setting the compatibility level to 2008 does not resolve this error. You will have to modify all your stored procedures and views.

如果您希望升级到SQL2014,则除了重新分解代码之外,没有其他解决方案。下面的示例演示了将兼容级别设置为2008并不能解决此错误。您必须修改所有存储过程和视图。

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100
GO
--DOESNOT WORK IN 2012 
DECLARE @ESTRecords TABLE
    (
     ESTime TIME(7) NOT NULL ,
     ESTDate DATE NOT NULL ,
     ESTDateTime AS ( CONVERT(DATETIME, ESTDate, ( 108 )) + ESTime )
       PERSISTED
    )

INSERT  INTO @ESTRecords
       ( ESTime, ESTDate )
VALUES  ( '12:00 PM', '12/31/2012' )

SELECT  *
FROM       @ESTRecords

#1

There is no other solution than re-factoring your code if you wish to upgrade to SQL2014. The example below demonstrates that setting the compatibility level to 2008 does not resolve this error. You will have to modify all your stored procedures and views.

如果您希望升级到SQL2014,则除了重新分解代码之外,没有其他解决方案。下面的示例演示了将兼容级别设置为2008并不能解决此错误。您必须修改所有存储过程和视图。

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100
GO
--DOESNOT WORK IN 2012 
DECLARE @ESTRecords TABLE
    (
     ESTime TIME(7) NOT NULL ,
     ESTDate DATE NOT NULL ,
     ESTDateTime AS ( CONVERT(DATETIME, ESTDate, ( 108 )) + ESTime )
       PERSISTED
    )

INSERT  INTO @ESTRecords
       ( ESTime, ESTDate )
VALUES  ( '12:00 PM', '12/31/2012' )

SELECT  *
FROM       @ESTRecords
标签：

相关文章