Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 702
Mhadonis Avatar asked Sep 29 '22 00:09

Mhadonis


1 Answers

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
like image 135
Alex Avatar answered Oct 03 '22 07:10

Alex