问题描述
大家，
在运行 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"?什么时候不能投射为日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！