SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before

Question

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]

Here dEvent and 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:

1. Why does that error not show on 2019? Did MS go back on that behavior and allow "simple temporal arithmetic"?
2. Why did that error not show up on 2016 the very first time the expression above was added to sproc (~2 years ago)?
3. 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.

2021-Aug-27: Update
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.

Answer 1

The error does occur on SQL Server 2019, as proven with this simple test.

Let's check the server version first:

SELECT [SQL Server Version] = @@VERSION;

Results:

SQL Server Version
Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64)
Jul 19 2021 15:37:34
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Red Hat Enterprise Linux)

The test code:

DECLARE @dt datetime = GETDATE();
DECLARE @d date = GETDATE();

SELECT [datetime] = @dt
    , [date] = @d;

The results:

datetime	date
2021-08-19 16:12:10.770	2021-08-19

The error behavior:

DECLARE @dt datetime = GETDATE();
DECLARE @d date = GETDATE();

SELECT @dt - @d;

and the error:

Msg 402, Level 16, State 1, Line 7
The data types datetime and date are incompatible in the subtract operator.

If I create a database in SQL Server 2019 with a compatibility level of 100 (which is SQL Server 2008 R2), the code runs fine:

CREATE DATABASE date_test;
ALTER DATABASE date_test SET COMPATIBILITY_LEVEL = 100;

USE date_test;
DECLARE @dt datetime = GETDATE();
DECLARE @d date = GETDATE();

SELECT @dt - @d;

The results:

(No column name)
1900-01-01 16:15:21.430

So, it seems likely that someone changed the compatibility level of your database. Check that with this statement:

SELECT d.name
    , d.compatibility_level
FROM sys.databases d
WHERE d.database_id = DB_ID();

The results:

name	compatibility_level
date_test	100

A datetime value can be implicitly converted to a time value because SQL Server has built-in implicit conversion code to do that since there is no chance for ambiguity when simply dropping the date portion of the value. Take a look at implicit conversions for a chart showing what can be implicitly converted and what needs an explicit conversion statement.