MS SQL:ISDATE() 是否应该返回“1"?什么时候不能投射为日期? [英] MS SQL: Should ISDATE() Return "1" when Cannot Cast as Date?

问题描述

大家，

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