IsDate 函数返回意外结果 [英] IsDate function returns unexpected results

问题描述

为什么 IsDate("13.50") 返回 True 但 IsDate("12.25.2010") 返回 False?

How come IsDate("13.50") returns True but IsDate("12.25.2010") returns False?

推荐答案

我最近被这个小功能"绊倒了，想提高对围绕 IsDate 函数的一些问题的认识VB 和 VBA.

I got tripped up by this little "feature" recently and wanted to raise awareness of some of the issues surrounding the IsDate function in VB and VBA.

如您所料，IsDate 在传递日期数据类型时返回 True，对于除字符串之外的所有其他数据类型返回 False.对于字符串，IsDate 根据字符串的内容返回 True 或 False:

As you'd expect, IsDate returns True when passed a Date data type and False for all other data types except Strings. For Strings, IsDate returns True or False based on the contents of the string:

IsDate(CDate("1/1/1980"))  --> True
IsDate(#12/31/2000#)       --> True
IsDate(12/24)              --> False  '12/24 evaluates to a Double: 0.5'
IsDate("Foo")              --> False
IsDate("12/24")            --> True

IsDateTime?

IsDate 应该更准确地命名为 IsDateTime 因为它返回 True 格式为时间的字符串:

IsDateTime?

IsDate should be more precisely named IsDateTime because it returns True for strings formatted as times:

IsDate("10:55 AM")   --> True
IsDate("23:30")      --> True  'CDate("23:30")   --> 11:30:00 PM'
IsDate("1:30:59")    --> True  'CDate("1:30:59") --> 1:30:59 AM'
IsDate("13:55 AM")   --> True  'CDate("13:55 AM")--> 1:55:00 PM'
IsDate("13:55 PM")   --> True  'CDate("13:55 PM")--> 1:55:00 PM'

请注意，从上面的最后两个示例中可以看出，IsDate 不是一个完美的验证器时间.

Note from the last two examples above that IsDate is not a perfect validator of times.

IsDate 不仅接受时间，还接受多种格式的时间.其中之一使用句点 (.) 作为分隔符.这会导致一些混淆，因为句点可以用作时间分隔符但不能用作日期分隔符:

Not only does IsDate accept times, it accepts times in many formats. One of which uses a period (.) as a separator. This leads to some confusion, because the period can be used as a time separator but not a date separator:

IsDate("13.50")     --> True  'CDate("13.50")    --> 1:50:00 PM'
IsDate("12.25")     --> True  'CDate("12.25")    --> 12:25:00 PM'
IsDate("12.25.10")  --> True  'CDate("12.25.10") --> 12:25:10 PM'
IsDate("12.25.2010")--> False '2010 > 59 (number of seconds in a minute - 1)'
IsDate("24.12")     --> False '24 > 23 (number of hours in a day - 1)'
IsDate("0.12")      --> True  'CDate("0.12")     --> 12:12:00 AM

如果您正在解析一个字符串并根据其明显类型对其进行操作，这可能是一个问题.例如:

This can be a problem if you are parsing a string and operating on it based on its apparent type. For example:

Function Bar(Var As Variant)
    If IsDate(Var) Then
        Bar = "This is a date"
    ElseIf IsNumeric(Var) Then
        Bar = "This is numeric"
    Else
        Bar = "This is something else"
    End If
End Function

?Bar("12.75")   --> This is numeric
?Bar("12.50")   --> This is a date

解决方法

如果您正在测试其基础数据类型的变体，您应该使用 TypeName(Var) = "Date" 而不是 IsDate(Var):

TypeName(#12/25/2010#)  --> Date
TypeName("12/25/2010")  --> String

Function Bar(Var As Variant)
    Select Case TypeName(Var)
    Case "Date"
        Bar = "This is a date type"
    Case "Long", "Double", "Single", "Integer", "Currency", "Decimal", "Byte"
        Bar = "This is a numeric type"
    Case "String"
        Bar = "This is a string type"
    Case "Boolean"
        Bar = "This is a boolean type"
    Case Else
        Bar = "This is some other type"
    End Select
End Function

?Bar("12.25")   --> This is a string type
?Bar(#12/25#)   --> This is a date type
?Bar(12.25)     --> This is a numeric type

但是，如果您正在处理可能是日期或数字的字符串(例如，解析文本文件)，您应该在检查它是否是日期之前先检查它是否是数字:

If, however, you are dealing with strings that may be dates or numbers (eg, parsing a text file), you should check if it's a number before checking to see if it's a date:

Function Bar(Var As Variant)
    If IsNumeric(Var) Then
        Bar = "This is numeric"
    ElseIf IsDate(Var) Then
        Bar = "This is a date"
    Else
        Bar = "This is something else"
    End If
End Function

?Bar("12.75")   --> This is numeric
?Bar("12.50")   --> This is numeric
?Bar("12:50")   --> This is a date

即使您只关心它是否是日期，您也应该确保它不是数字:

Even if all you care about is whether it is a date, you should probably make sure it's not a number:

Function Bar(Var As Variant)
    If IsDate(Var) And Not IsNumeric(Var) Then
        Bar = "This is a date"
    Else
        Bar = "This is something else"
    End If
End Function

?Bar("12:50")   --> This is a date
?Bar("12.50")   --> This is something else

CDate 的特点

正如@Deanna 在下面的评论中指出的那样，CDate() 的行为也不可靠.其结果因传递的是字符串还是数字而异:

Peculiarities of CDate

As @Deanna pointed out in the comments below, the behavior of CDate() is unreliable as well. Its results vary based on whether it is passed a string or a number:

?CDate(0.5)     -->  12:00:00 PM
?CDate("0.5")   -->  12:05:00 AM

如果数字作为字符串传递，则尾随 和 前导零很重要:

Trailing and leading zeroes are significant if a number is passed as a string:

?CDate(".5")    -->  12:00:00 PM 
?CDate("0.5")   -->  12:05:00 AM 
?CDate("0.50")  -->  12:50:00 AM 
?CDate("0.500") -->  12:00:00 PM 

随着字符串的小数部分接近 60 分钟标记，行为也会发生变化:

The behavior also changes as the decimal part of a string approaches the 60-minute mark:

?CDate("0.59")  -->  12:59:00 AM 
?CDate("0.60")  -->   2:24:00 PM 

最重要的是，如果您需要将字符串转换为日期/时间，您需要了解您希望它们采用的格式，然后在依赖 CDate() 之前适当地重新格式化它们代码> 转换它们.

The bottom line is that if you need to convert strings to date/time you need to be aware of what format you expect them to be in and then re-format them appropriately before relying on CDate() to convert them.

这篇关于IsDate 函数返回意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！