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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 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:
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:
I did this same tests on Oracle Database and MySQL and the two banks were able to perform the account correctly, as these banks already do 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 take charge of thinking about this type 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:
That's it folks!
Thanks for stopping by and see you in the next post.
