In one of stored procs I need to capture the duration of a live event. It is guaranteed to never exceed 24 hours, so the datatype for the result column is chosen as
TIME(3) (mapping to
TimeSpan on application side). Calculation happens like so:
update tbEvent set tWait= @dtEvent - dEvent - tEvent [where idEvent = @idEvent]
tEvent are previously set to the date and time portions of the
dtEvent (moment of the origin event), and
@dtEvent is the moment of a subsequent closure event. So I subtract a DATE and a TIME column-values from a DATETIME variable and stuff the result into a TIME column.
[Reasonably?] expected behavior: SQL implicitly casts each expression term to DATETIME, performs subtraction, and then implicitly casts the result to TIME by simply dropping DATE portion. This has always worked! Well, - so far..
An update script suddenly produced an error on SQL 2016 (13.0.1742.0):
System.Data.SqlClient.SqlException (0x80131904): The data types datetime and date are incompatible in the subtract operator. Error Number:402,State:1,Class:16
But the same script ran fine on 2008 R2 (10.50.2500.0) and 2019 (15.0.2080.9)!
This stored procedure has been updated with this code for quite some time (couple years), and subsequent modifications to it (with that code) have been successfully applied on all of these staging servers several times. What gives?
The first promising and to-the-point explanation found was this article (from 2013), which points that starting with SQL Server 2012 Microsoft does not allow adding/ subtracting a TIME from a DATETIME without explicit cast (though no direct reference is given). MS's own docs (DATETIME, DATE, TIME) do not even mention direct arithmetic operations.
The resolution, as suggested, is simple - use explicit cast to DATETIME (works on all versions):
update tbEvent set tWait= @dtEvent - cast(dEvent as datetime) - cast(tEvent as datetime)
I don't have a 2012, or 2014 and 2017 versions to do a complete check. But I do have these questions:
- Why does that error not show on 2019? Did MS go back on that behavior and allow "simple temporal arithmetic"?
- Why did that error not show up on 2016 the very first time the expression above was added to sproc (~2 years ago)?
- Why there's no need of explicit cast of a DATETIME value being shoved into a TIME column?
The QA team says there were no recent SQL Server updates on 2016 box.
Hannah's answer is spot on (covers Q#1,2):
On 2016 box DB's compatibility level was set to 130 (i.e. 2016) sometime not so long ago.
Initial value is 100 (2008 R2), that is why the error does not get triggered on our 2019 box.
Q#3 remains open. I believe it is related and would like to have a clue for completeness.