問題描述
大家，
在運行 2012 SP3 的 SQL Server 實例上，以下代碼返回1":
On a SQL Server instance running 2012 SP3, the following code returns "1":
SELECT ISDATE('january,25,1999')
但是，以下轉換失敗:
SELECT CAST('january,25,1999' AS DATE)
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
我是否誤解了 ISDATE() 應該返回什么?為什么 ISDATE() 為字符串返回值1"?我們應該如何從 SQL Server 中確定諸如january,25,1999"之類的字符串是否可以轉換為日期?
Am I misunderstanding what ISDATE() ought to return? Why does ISDATE() return a value of "1" for the string? How ought we determine, from SQL Server, whether strings such as "january,25,1999" can be casted as dates?
謝謝！
推薦答案
忘記 ISDATE()！您使用的是 SQL Server 2012:
Forget ISDATE()! You are using SQL Server 2012:
SELECT TRY_CONVERT(date, 'january,25,1999')
如果日期無法轉換，這將返回 NULL - 我認為這不可能.您可以為要轉換的特定格式指定第三個參數.
This will return NULL if the date cannot be converted -- which I don't think this can be. You can specify a third argument for the particular format you want to convert.
我會注意到 SQL Server 2014 中的一些內容:
I will note something that in SQL Server 2014:
SELECT TRY_CONVERT(date, 'january,25,1999')
返回錯誤.
SELECT TRY_CONVERT(datetime, 'january,25,1999')
成功.
我不知道為什么.但是對于你想要的:
I have no idea why. But for what you want:
SELECT CONVERT(DATE, TRY_CONVERT(datetime, 'january,25,1999'))
這篇關于MS SQL:ISDATE() 是否應該返回“1"?什么時候不能投射為日期?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！