5

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mhadonis
  • 330
  • 3
  • 11
  • 2
    Can you show us some sample code with your issue? – Paddy May 11 '15 at 10:58
  • 1
    Does it work when you change the compatibility mode back to 2008 ? `ALTER DATABASE database_name SET COMPATIBILITY_LEVEL 100` – Alex May 11 '15 at 11:01

1 Answers1

4

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
Alex
  • 21,273
  • 10
  • 61
  • 73
  • 2
    this seems to be a quirk of table variables. For permanent tables or expressions like `SELECT CAST(GETDATE() AS date) + GETDATE()` the compatibility level works. Probably table variables work off compat of tempdb – Martin Smith Jan 30 '19 at 17:36