SQL Server 2016 突然產生 Error:402,State:1,Class:16 用 DATETIME/DATE/TIME 算法更新儲存過程，這在以前很好

在其中一個儲存過程中，我需要擷取實時事件的持續時間。保證永遠不會超過 24 小時，因此結果列的數據類型選擇為TIME(3)（映射到TimeSpan應用程序端）。計算如下：

update tbEvent set tWait= @dtEvent - dEvent - tEvent [where idEvent = @idEvent]

這里dEvent和之前被設置為（起源事件的時刻）tEvent的日期和時間部分，並且是後續關閉事件的時刻。因此，我從 DATETIME 變數中減去 DATE 和 TIME 列值，並將結果填充到 TIME 列中。dtEvent``@dtEvent

$$ Reasonably? $$預期行為： SQL 將每個表達式項隱式轉換為 DATETIME，執行減法，然後通過簡單地刪除 DATE 部分將結果隱式轉換為 TIME。 這一直有效！ 嗯， - 到目前為止..

---

更新腳本突然在 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

但是相同的腳本在 2008 R2 (10.50.2500.0) 和 2019 (15.0.2080.9) 上執行良好！

這個儲存過程已經用這個程式碼更新了很長一段時間（幾年），並且對它的後續修改（用那個程式碼）已經成功地應用在所有這些登台伺服器上幾次。是什麼賦予了？

---

發現的第一個有希望且中肯的解釋是這篇文章（從 2013 年開始），它指出從 SQL Server 2012 開始，微軟不允許在沒有顯式轉換的情況下從 DATETIME 中添加/減去 TIME（儘管沒有給出直接參考） . MS自己的文件（DATETIME，DATE，TIME）甚至沒有提到直接的算術運算。

正如建議的那樣，解決方案很簡單 - 使用顯式轉換為 DATETIME（適用於所有版本）：

update tbEvent set tWait= @dtEvent - cast(dEvent as datetime) - cast(tEvent as datetime)

我沒有 2012 或 2014 和 2017 版本來進行完整檢查。但我確實有這些問題：

1. 為什麼該錯誤未在 2019 年顯示？MS是否回到了這種行為並允許“簡單的時間算術”？
2. 為什麼在 2016 年第一次將上述表達式添加到 sproc（約 2 年前）時，該錯誤沒有出現？
3. 為什麼不需要將 DATETIME 值顯式轉換為 TIME 列？

QA 團隊表示，2016 年的機器最近沒有 SQL Server 更新。

2021 年 8 月 27 日：更新

Hannah 的答案很明確（涵蓋 Q#1,2）：

不久前的某個時間，2016 年 box DB 的兼容性級別設置為 130（即 2016 年）。

初始值為 100 (2008 R2)，這就是為什麼我們的 2019 盒子上沒有觸發錯誤。

Q#3 保持打開狀態。 我相信它是相關的，並希望有一個完整的線索。

正如這個簡單的測試所證明的那樣，SQL Server 2019 上確實發生了該錯誤。

我們先檢查一下伺服器版本：

SELECT [SQL Server Version] = @@VERSION;

結果：

測試程式碼：

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

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

結果：

錯誤行為：

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

SELECT @dt - @d;

和錯誤：

消息 402，第 16 層，狀態 1，第 7 行
數據類型 datetime 和 date 在減法運算符中不兼容。

如果我在 SQL Server 2019 中創建一個兼容級別為 100 的數據庫（即 SQL Server 2008 R2），則程式碼執行良好：

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;

結果：

因此，似乎有人更改了您的數據庫的兼容性級別。使用以下語句檢查：

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

結果：

可以將datetime值隱式轉換為time值，因為 SQL Server 具有內置的隱式轉換程式碼來執行此操作，因為在簡單地刪除值的日期部分時不會出現歧義。查看圖表的隱式轉換，該圖表顯示了可以隱式轉換的內容以及需要顯式轉換語句的內容。

引用自：https://dba.stackexchange.com/questions/298282