#0239 – SQL Server 2012 – Msg 402 – The data types datetime and time are incompatible in the add/subtract operator


Microsoft SQL Server 2008 came with a wide array of T-SQL enhancements. One of them was the ability to split a DATETIME value into DATE and TIME values. From a storage standpoint this allowed us to store and bind (on the UI) date and time values separately, while appending the values when displaying on a report or exporting to a 3rd party system. They are also useful to store time revisions with respect to a DATETIME value.

Here’s a quick example:

SQL Server 2008/2008 R2

In the query below, I will be taking two variables – a DATETIME and a TIME value and then add and subtract the TIME value from the DATETIME value to get the final result.

--Please execute on a SQL 2008/2008 R2 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + @tTime) AS DateTimeAddResult,
       (@tDate - @tTime) AS DateTimeSubtractResult;
GO

The output of the above query is shown below:

image

As you can see, we were able to successfully add and subtract the DATE and the TIME values to create corresponding DATETIME values.

Changes in SQL Server 2012

If your application uses code similar to the one shown above, it will not work as expected in SQL Server 2012 (SQL 11/”Denali”):

--Please execute on a SQL 2012 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + @tTime) AS DateTimeAddResult;

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate - @tTime) AS DateTimeSubtractResult;
GO

The following error message is encountered:

Msg 402, Level 16, State 1, Line 6

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

Msg 402, Level 16, State 1, Line 10

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

As you can see, SQL Server 2012 no longer allows us to add/subtract a TIME value from a DATETIME value using the conventional Add (+) and Subtract (-) operators.

Workaround

A workaround to this issue is to modify the code such that the TIME value is converted to a DATETIME value before the Add (+) or Subtract (-) operation takes place.

--Please execute on a SQL 2012 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + CAST(@tTime AS DATETIME)) AS DateTimeAddResult,
       (@tDate - CAST(@tTime AS DATETIME)) AS DateTimeSubtractResult;
GO

image

Conclusion

Between just two releases, this is a considerably major change because one may have a considerable number of objects written using this ability of DATETIME and DATE/TIME data-types to add/subtract. With an upgrade of the database to SQL Server 2012, these objects will need to be modified. I would therefore present the following questions before you:

  • Have you faced this issue in your migration from SQL Server 2008 to SQL Server 2012?
  • What solution/workaround did you apply?

Reference:

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

6 thoughts on “#0239 – SQL Server 2012 – Msg 402 – The data types datetime and time are incompatible in the add/subtract operator

  1. ramkoti

    Hi Nakul,
    Can we restore Sql server 2012 backup on Sql server 2008 R2 ?
    If not, What is the solution for this ?, Client want to demigrate from Sql server 2012 to Sql server 2008 R2.

    Like

    Reply
  2. Nakul Vachhrajani

    @Ramkoti: I moved the question to the ASK module. You can find the question and the anser here: [http://beyondrelational.com/modules/3/ask/questions/18956/how-to-restore-a-sql-server-2012-backup-to-sql-server-2008-r2-instance.aspx][1]

    The question that keeps bothering me is: Why does the customer want to downgrade from SQL Server 2012 to SQL Server 2008 R2? Any specific reasons that can be shared? Knowing the reasons may help the community in general.

    [1]: http://beyondrelational.com/modules/3/ask/questions/18956/how-to-restore-a-sql-server-2012-backup-to-sql-server-2008-r2-instance.aspx

    Like

    Reply
  3. Tiago Meireles

    I did this and it worked on SQL Server 2016:

    CAST(T.[Date] as Datetime) + Cast(Cast(T.[Hour] as Time) as datetime)

    Like

    Reply
    1. nakulvachhrajani Post author

      Hello, Tiago! Can you please check the compatibility level of your database? If the compatibility level is 130, it would not work. For example, this does not work on a database with compatibility level 110 or higher:

      DECLARE @currentDate DATETIME = GETDATE();
      DECLARE @hours TIME = ’02:00:00.0000000′;

      SELECT CAST(@currentDate AS DATETIME) + CAST(@hours AS TIME);

      Like

      Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s