SQL Server - The Data Types Datetime and Time Are Incompatible in the Add Operator

Hey guys!
All jewelry?

After almost 15 days without posting due to several projects I was working on, I found some time to make this quick post for you about an error I found when adding a DATETIME column to a TIME column. This type of operation normally occurred when the database's compatibility mode was set to 100 (SQL), but when changing the compatibility mode to 110 (SQL 2012), 120 (SQL 2014) or higher, some jobs that I manage began to display 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 a column of type DATETIME and another of type TIME, but I think the test is more practical if we create the select manually, as in the script below:

USE [Testes]
GO

PRINT 'Horário atual: ' + CONVERT(VARCHAR(19), GETDATE())

ALTER DATABASE Testes SET COMPATIBILITY_LEVEL = 100 -- SQL 2008
GO

PRINT GETDATE() + CAST('00:12:13' AS TIME)
GO

ALTER DATABASE Testes SET COMPATIBILITY_LEVEL = 110 -- SQL 2012
GO

PRINT GETDATE() + CAST('00:12:13' AS TIME)
GO

ALTER DATABASE Testes SET COMPATIBILITY_LEVEL = 120 -- SQL 2014
GO

PRINT GETDATE() + CAST('00:12:13' AS TIME)
GO

Why does this error occur?

This error started to occur because Microsoft made improvements to the Database Engine starting with SQL Server 2012, and the idea of ​​performing operations between variables of different data types without an explicit conversion (leaving the database to 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 data of type INT and FLOAT without an explicit conversion:

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

Always ensure that the types of data involved are compatible with the operations performed:

I carried out the same tests on Oracle Database and MySQL and both banks were able to perform the calculation correctly, as these banks already perform the implicit conversion from integer to float in cases of division and multiplication, something that SQL Server does not do. For this reason, I always recommend that the Query developer takes charge of thinking about this type of situation, and do not let the bank “think” for you.

I've had personal experience and seen how disastrous management reports can be that have this type of flaw between INT and FLOAT, especially in rates and percentage indicators.

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

How can I get around this limitation and solve the problem?

Well, I've already found several amazing solutions on the internet, various string treatments and other not very performant things, when there is such a simple and silly solution to solve this, which is to convert the TIME column to DATETIME:

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