Parse data and time string to Access Date value

Question

How can I parse a date/time string into an Access Date object given a certain date- and time format?

I can use the CDate() function like this:

  Dim StrDateTime As String
  Dim DtTest As Date

  StrDateTime = "2011-12-31 23:59:59"

  DtTest = CDate(StrDateTime)
  MsgBox DtTest

This works, Access recognizes the format, fine, but how can I absolutely be sure that this happens under all circumstances (e.g. Date/Time settings Regional Settings, Access version?). I would like to "tell" CDate my special date/time format.

Other option is this (but a lot of code):

  Dim StrDateTime As String
  Dim IntYear As Integer
  Dim IntMonth As Integer
  Dim IntDay As Integer
  Dim IntHour As Integer
  Dim IntMinute As Integer
  Dim IntSecond As Integer

  StrDateTime = "2011-12-31 23:59:59"

  IntYear = Val(Mid(StrDateTime, 1, 4))
  IntMonth = Val(Mid(StrDateTime, 6, 2))
  IntDay = Val(Mid(StrDateTime, 9, 2))
  IntHour = Val(Mid(StrDateTime, 12, 2))
  IntMinute = Val(Mid(StrDateTime, 15, 2))
  IntSecond = Val(Mid(StrDateTime, 18, 2))

  DtTest = DateSerial(IntYear, IntMonth, IntDay)
  DtTest = DtTest + TimeSerial(IntHour, IntMinute, IntSecond)
  MsgBox DtTest

Other advantage of CDate(): it give a Type Mismatch error on a wrong date/time value. DateSerial + TimeSerial recalculates a new date and time, so "2011-12-31 24:59:59" becomes 01/Jan/2012 0:59:59.

CDate() honours the Regional Settings, regardless of version of Access. One disadvantage of CDate() is that it does not always give a Type Mismatch when an ambiguous text value does not match Regional Settings e.g. my settings are UK, Month(CDate("28/04/2011")) correctly returns 4 (April) but the absurd UK value Month(CDate("04/28/2011")) also returns 4 when arguably an error would be more useful. — onedaywhen
Well, what is the definition of an "unambiguous format"? The fact that it's recognized on my computer with my settings doesn't necessarily means it will in every situation? Or am I wrong? Is "yyyy-mm-dd hh:mm:ss" (and other known formats) universal to all systems? — waanders
Is this documented somewhere? And is there no way to explicitly tell CDate() which parse format to use, something like CDate("yyyy-mm-dd hh:mm", StrValue)? — waanders
In addition to the answer by HansUp above, you may also be interested in this related StackOverflow discussion about parsing dates from text. — JJ Brown

HansUp HansUp · Accepted Answer · 2011-09-26T14:43:39

The format "yyyy-mm-dd" is an ISO standard and when encountering it, CDate() expects the "yyyy" part to be followed by "mm-dd", never by "dd-mm". So a date string in that format is unambiguous; it represents the same date value regardless of the user's locale. And CDate() will comply.

Furthermore, there is no way to give CDate() a date string formatted as "yyyy-dd-mm" and get the date value you intend back. So CDate("2011-02-01") will always give you the date value #Feb 1 2011# (regardless of your locale), even if you intended that string to represent #Jan 2 2011#. And CDate("2011-31-01") will throw a type mismatch error.

Also note that this only works for dates after the year 100. (See Heinzi's comment.)

Parse data and time string to Access Date value

1 Answers