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

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,

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.

StackExchange Database Administrators Q#298282, answer score: 10