SQL Server: From 2008 To 2014 :-The data types datetime and time are incompatible in the add operator. Any other solution?

Question

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

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

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

Is there any other solution for above problem?

Answer 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.

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