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
– onedaywhen, Commented Sep 16, 2011 at 15:13
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? — wndrs
– wndrs, Commented Sep 16, 2011 at 19:39
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)? — wndrs
– wndrs, Commented Sep 17, 2011 at 13:44
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
– JJ Brown, Commented Feb 20, 2019 at 18:11

HansUp · Accepted Answer · 2013-04-02 14:42:53Z

8

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.)

edited Apr 2, 2013 at 14:42

answered Sep 26, 2011 at 14:43

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Heinzi Over a year ago

Note that this only works for dates after the year 100: CDate("0001-02-03") yields the 1st Feb. 2003.

Collectives™ on Stack Overflow

Parse data and time string to Access Date value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related