關於 ISDATE 函式和 DATENAME 函式的問題

今天早上我們遇到了一個問題，報告由於以下錯誤而失敗：

“從字元串轉換日期和/或時間時轉換失敗”

問題是在數據庫中我們的值（在 VARCHAR 欄位中）為：‘31.07/2021’

該報告使用 ISDATE 檢查來驗證該值實際上是一個日期，如果是，則使用 DATENAME 函式將其分開（以得到類似於“DD-MMM-YYYY”的格式。

問題是即使 ISDATE 為該值返回 1（表明它被 SQL Server 解釋為日期），後續的 DATENAME 函式也會引發轉換錯誤。

這是在 SQL Server 2008 R2 上，但也在 SQL 2012 和 SQL 2016 上進行了測試。

複製程式碼：

set dateformat dmy;

-- Date value from the database - note the mixture of '.' and '/' as separators
declare @the_date varchar(20) = '31.07/2021';

-- should return 1 - SQL interprets the above as a date
select isdate(@the_date);

-- All three of the DATENAME functions fail - why?
select DATENAME(dd,@the_date);

select DATENAME(MONTH,@the_date);

select DATENAME(YYYY,@the_date);

我原以為如果一個值從 ISDATE 返回 1，那麼任何後續與日期相關的函式都應該正確解釋該值。顯然我錯過了一些東西。:-)

如果有人可以解釋這一點，那將非常有幫助。

ISDATE()可能是一個棘手的函式，您最好使用顯式TRY_CONVERT()函式來測試值是否正確格式化為日期。

我的猜測是，您指定的範例值被視為帶時間的日期，這就是為什麼ISDATE()返回 true，類似於本文中的問題。

SQL Server 數據類型和函式可能很棘手。

讓我們從ISDATE()函式開始。文件相當簡潔地描述了該功能：

如果表達式是有效的日期、時間或日期時間值，則返回 1 ；否則為 0。

即使該函式是ISDATE()，它可能會更好地呼叫ISDATEORTIMEORDATETIME()。我猜後者的名字只是沒有通過行銷部門。

這意味著您擁有的字元串 (‘31.07/2021’) 將轉換為 a date、time或datetimevalue。如果它成功轉換為這些值中的任何一個，ISDATE()則將返回 true。

如果我們查看函式的文件，DATENAME()我們會看到在語法中，第二個參數具體是一個date值：

日期名稱（日期部分，日期）

您有這三個查詢，其中@the_date是一個varchar值：

SELECT DATENAME(dd,@the_date);
SELECT DATENAME(MONTH,@the_date
SELECT DATENAME(YYYY,@the_date);

但是因為DATENAME()需要輸入日期，所以它會隱式執行此操作：

SELECT DATENAME(dd,CONVERT(date,@the_date));
SELECT DATENAME(MONTH,CONVERT(date,@the_date));
SELECT DATENAME(YYYY,CONVERT(date,@the_date));

並且隱式轉換date失敗並出現您看到的錯誤。這意味著該值成功轉換為 atime或datetime成功。並且因為它成功轉換為其中之一，所以ISDATE()返回 true。

我們可以使用TRY_CONVERT()代替ISDATE(). TRY_CONVERT()更精確，因為它只會嘗試轉換為指定的非常具體的轉換，而不是ISDATE().

通過這樣做，我們看到轉換為date和time失敗，但轉換為datetime成功。

SET DATEFORMAT dmy;
DECLARE @the_date varchar(20) = '31.07/2021';

SELECT  TRY_CONVERT(date,@the_date),
       TRY_CONVERT(time,@the_date),
       TRY_CONVERT(datetime,@the_date);

如果要將字元串轉換為日期，則確實應該使用顯式格式程式碼進行轉換，因為唯一真正安全的隱式轉換格式是YYYYMMDD. 所有其他日期格式可能會有所不同。

也就是說，如果您願意在日期格式上擲骰子，或者正在獲得各種和不可預測的日期格式，您可以在隱式轉換上擲骰子，並解決您看到的問題。

您可以簡單地將varchar日期字元串放入一個date值中，然後將該date值傳遞給DATENAME. 您可以利用TRY_CONVERT嘗試將字元串轉換為兩者date，datetime並在可行的情況下使用一個。像這樣的東西適用於各種古怪的格式，只要它可以轉換為 EITHERdate或datetime：

SET DATEFORMAT dmy;
DECLARE @the_date_varchar varchar(20) = '31.07/2021';

DECLARE @the_date_date date;

SELECT @the_date_date = COALESCE(TRY_CONVERT(date,@the_date_varchar),
                            TRY_CONVERT(datetime,@the_date_varchar)
                            );

SELECT @the_date_date

SELECT  DATENAME(dd,@the_date_date),
       DATENAME(MONTH,@the_date_date),
       DATENAME(YYYY,@the_date_date);

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