vba 将数据和时间字符串解析为访问日期值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7442648/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parse data and time string to Access Date value
提问by waanders
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:
我可以像这样使用 CDate() 函数：
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.
这有效，Access 可以识别格式，很好，但是我如何绝对确保在所有情况下都会发生这种情况（例如日期/时间设置区域设置、Access 版本？）。我想“告诉”CDate 我的特殊日期/时间格式。
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() 的其他优点：它在错误的日期/时间值上给出类型不匹配错误。DateSerial + TimeSerial 重新计算新的日期和时间，因此“2011-12-31 24:59:59”变为 01/Jan/2012 0:59:59。
回答by HansUp
The format "yyyy-mm-dd"is an ISO standardand 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.
格式"yyyy-mm-dd"是ISO 标准，当遇到它时，CDate()期望"yyyy"部分后跟"mm-dd"，而不是"dd-mm"。因此，该格式的日期字符串是明确的；无论用户的语言环境如何，它都表示相同的日期值。并且CDate()会遵守。
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.
此外，无法提供CDate()格式为“yyyy-dd-mm”的日期字符串并获取您想要返回的日期值。因此，CDate("2011-02-01")将永远给你的日期值#Feb 1 2011#（无论您的区域设置的），即使你打算字符串来表示#Jan 2 2011#。并且CDate("2011-31-01")会抛出类型不匹配错误。
Also note that this only works for dates after the year 100. (See Heinzi's comment.)
另请注意，这仅适用于 100 年后的日期。（请参阅 Heinzi 的评论。）
