SQL Server - The data types datetime and time are incompatible in the add operator

Views: 814
Reading Time: 3 minutes

Hello everybody!
All jewel?

After almost 15 days without posting on various jobs I was working on, I got some time to make this quick post for you about a mistake I found when adding a DATETIME column with a TIME type. This type of operation usually occurred when the bank compatibility mode was in 100 (SQL), but when I changed the compatibility mode to 110 (SQL 2012), 120 (SQL 2014) or higher, some jobs I run started the following error message:

Msg 402, Level 16, State 1, Line 1
The data types datetime and time are incompatible in the add operator.

Simulating the error

There are several ways to simulate, such as creating a table with one column of type DATETIME and another of type TIME, but I think the test is more practical if we create select manually, as in the script below:

SQL Server - The data types datetime and time are incompatible in the add operator

Why does this error occur?

This error occurred because Microsoft made improvements to the database engine from SQL Server 2012, and the idea of ​​performing operations between variables of different data types without explicit conversion (letting the bank do the implicit conversion) is never a good idea.

A classic example that this practice should not be used can be seen when working with INT and FLOAT data without an explicit cast:
SQL Server - The data types datetime and time are incompatible in the add operator 2

By the universal definition of mathematics, the way SQL Server performs this simple account is WRONG. This is because when dividing 1 by 2, SQL Server splits considering that the 2 numbers are integers, so the result must also be integer, causing 1 / 2, which is 0.5, to be returned as 0. and then added 1.

Always ensure that the types of data involved are compatible with the operations performed:
SQL Server - The data types datetime and time are incompatible in the add operator 3

I did this same tests on Oracle Database and MySQL and both banks were able to do the account correctly, as these banks already do the implicit integer to float conversion in cases of division and multiplication, which SQL Server does not do. For this reason, I always recommend that the Query developer take care of thinking about this kind of situation, and don't let the bank "think" for you.

I have had personal experiences and saw how disastrous management reports can be that this kind of failure exists between INT and FLOAT, especially in rates and percentage indicators.

That said, Microsoft has been trying to reduce this kind of misconception and no longer allows operations involving DATETIME and TIME using simple (+) and (-) operators, only with conversions like CAST and CONVERT, where you can (and should) set the style. of the conversion that will be performed, avoiding the loss of data accuracy when converted.

How to work around this limitation and resolve the issue?

Well, I've found a lot of mind-boggling solutions on the internet, a lot of string treatments, and other not-so-performative things, when you have such a simple and silly solution to solve this, which is to convert the TIME column to DATETIME:

SQL Server - The datetime data types and time are incompatible in the add operator solved

That's it folks!
Thanks for stopping by and see you in the next post.