Parse data and the time string to the value "Access Date"

Question

How can I parse a date / time string in a Date Access object, given a specific date and time format?

I can use the CDate () function as follows:

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 be absolutely sure that this happens under any circumstances (for example, date / time settings Regional Settings, Access version?). I would like to “tell” CDate my special date / time format.

Another option is (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 

Another advantage of CDate (): it gives a type mismatch error with an incorrect date / time value. DateSerial + TimeSerial recounts the new date and time, so "2011-12-31 24:59:59" becomes 01 / Jan / 2012 0:59:59.

Answer 1

The yyyy-mm-dd format is an ISO standard , and upon meeting it, CDate() expects the yyyy part to follow the mm-dd, never dd-mm. Therefore, the date string in this format is unambiguous; it represents the same date value regardless of the user locale. And CDate() will match.

In addition, there is no way to give CDate() date string formatted as "yyyy-dd-mm" and get the date value that you intend to return. That way, CDate("2011-02-01") will always tell you the date value #Feb 1 2011# (regardless of your locale), even if you specified this line for the #Jan 2 2011# view. And CDate("2011-31-01") will CDate("2011-31-01") type mismatch error.

Also note that this only works for dates after 100 years. (See Heinzie's comment.)