将数据和时间字符串解析为“访问日期”值(Parse data and time string to Access Date value)

将数据和时间字符串解析为“访问日期”值(Parse data and time string to Access Date value)

在给定特定日期和时间格式的情况下，如何将日期/时间字符串解析为Access Date对象？

我可以像这样使用CDate（）函数：

Dim StrDateTime As String Dim DtTest As Date StrDateTime = "2011-12-31 23:59:59" DtTest = CDate(StrDateTime) MsgBox DtTest

这可行，Access识别格式，很好，但我怎么能完全确定在所有情况下都会发生这种情况（例如日期/时间设置区域设置，访问版本？）。 我想“告诉”CDate我的特殊日期/时间格式。

其他选项是这个（但很多代码）：

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

CDate（）的其他优点：它在错误的日期/时间值上给出类型不匹配错误。 DateSerial + TimeSerial重新计算新的日期和时间，因此“2011-12-31 24:59:59”将成为01 / Jan / 2012 0:59:59。

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.

最满意答案

格式“yyyy-mm-dd”是ISO标准 ，当遇到它时， CDate()期望“yyyy”部分后跟“mm-dd” ，而不是“dd-mm” 。 所以这种格式的日期字符串是明确的; 无论用户的语言环境如何，它都表示相同的日期值。 并且CDate()将遵守。

此外，无法为CDate()提供格式为“yyyy-dd-mm”的日期字符串，并获取您想要的日期值。 因此， CDate("2011-02-01")将始终为您提供日期值#Feb 1 2011# （无论您的语言环境如何），即使您打算使用该字符串来表示#Jan 2 2011# 。 而CDate("2011-31-01")将抛出类型不匹配错误。

另请注意，这仅适用于100年后的日期。（请参阅Heinzi的评论。）

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