StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
4338028
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
6
CommunityOwnedDate
CreationDate
2010-12-02T17:41:25.830
FavoriteCount
0
LastActivityDate
2014-08-13T14:00:48.597
LastEditDate
2014-08-13T14:00:48.597
LastEditorUserId
154439
OwnerUserId
154439
ParentId
4338025
PostTypeId
2
Score
58
ViewCount
0
LastEditorDisplayName
text
Body
<p>I got tripped up by this little "feature" recently and wanted to raise awareness of some of the issues surrounding the <code>IsDate</code> function in VB and VBA.</p> <h2>The Simple Case</h2> <p>As you'd expect, <code>IsDate</code> returns <code>True</code> when passed a Date data type and <code>False</code> for all other data types except Strings. For Strings, <code>IsDate</code> returns <code>True</code> or <code>False</code> based on the contents of the string:</p> <pre><code>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 </code></pre> <h2>IsDateTime?</h2> <p><code>IsDate</code> should be more precisely named <code>IsDateTime</code> because it returns <code>True</code> for strings formatted as times:</p> <pre><code>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' </code></pre> <p>Note from the last two examples above that <code>IsDate</code> is not a perfect <em>validator</em> of times.</p> <h2>The Gotcha!</h2> <p>Not only does <code>IsDate</code> accept times, it accepts times in many formats. One of which uses a period (<code>.</code>) as a separator. This leads to some confusion, because the period can be used as a time separator but not a date separator:</p> <pre><code>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 </code></pre> <p>This can be a problem if you are parsing a string and operating on it based on its apparent type. For example:</p> <pre><code>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 </code></pre> <h2>The Workarounds</h2> <p>If you are testing a variant for its underlying data type, you should use <code>TypeName(Var) = "Date"</code> rather than <code>IsDate(Var)</code>:</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>Even if all you care about is whether it is a date, you should probably make sure it's not a number:</p> <pre><code>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 </code></pre> <h2>Peculiarities of CDate</h2> <p>As @Deanna pointed out in the comments below, the behavior of <code>CDate()</code> is unreliable as well. Its results vary based on whether it is passed a string or a number:</p> <pre><code>?CDate(0.5) --> 12:00:00 PM ?CDate("0.5") --> 12:05:00 AM </code></pre> <p>Trailing <em>and</em> leading zeroes are significant if a number is passed as a string:</p> <pre><code>?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 </code></pre> <p>The behavior also changes as the decimal part of a string approaches the 60-minute mark:</p> <pre><code>?CDate("0.59") --> 12:59:00 AM ?CDate("0.60") --> 2:24:00 PM </code></pre> <p>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 <code>CDate()</code> to convert them.</p>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POIsDate function returns unexpected results
  singulars
  PostTypePostTypeId
  PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USmwolfe02
UserOwnerUserId
1. USmwolfe02
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POIsDate function returns unexpected results
  singulars
  PostTypePostTypeId
  PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
2. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
3. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
CommentsPostId
1. COThat... was impressively complete.
  singulars
  PostPostId
  PO
  UserUserId
  USvariant
2. COIsDate() works this way in VB.Net too.
  singulars
  PostPostId
  PO
  UserUserId
  USJorriss
3. CO@Joriss: Good to know.
  singulars
  PostPostId
  PO
  UserUserId
  USmwolfe02

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.