vba 在访问中将文本转换为日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7263766/
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
Convert a Text to a DateTime in access
提问by James213
So i have a field that datatype is a text which is feed into the database. What it returns is something along the lines of this:
所以我有一个字段，数据类型是输入数据库的文本。它返回的内容大致如下：
ddd MMM dd hh:mm:ss yyyy
ddd MMM dd hh:mm:ss yyyy
What i would like for it to do is be displayed as something like this:
我希望它做的是显示为这样的：
ddd MMM dd yyyy hh:mm:ss
ddd MMM dd yyyy hh:mm:ss
I can achive this by using Format() which would look like this:
我可以通过使用 Format() 来实现这一点，它看起来像这样：
Format(alarmdet.AlarmStart, "ddd MMM dd yyyy hh:mm:ss) AS AlarmDateTime
So that is all well and good, however; i want to beable to convert this value into a datetime. I've tried using CVDate, CDate, DateValue and every time i get returned an error claiming a mismatched datatype. How would i go about converting this exact string into a datetime?
然而，这一切都很好。我希望能够将此值转换为日期时间。我试过使用 CVDate、CDate、DateValue，每次我返回一个错误，声称数据类型不匹配。我将如何将这个确切的字符串转换为日期时间？
Note:
笔记：
So you are aware, i am able to get it to convert successfully when in the English(united states) locale, but i am attempting to get this to work in the Portuguese(portugal) locale. In this locale i get the mismatch datatype error which i think has something to do with how access reads the abrivated months. Is there something i am missing to make this successfully work in an international setting?
所以你知道，我能够在英语（美国）语言环境中成功转换，但我试图让它在葡萄牙语（葡萄牙）语言环境中工作。在这个语言环境中，我得到了不匹配数据类型错误，我认为这与访问读取缩写月份的方式有关。在国际环境中，我是否缺少使这项工作成功的东西？
Also i would like to convert something similar to this in a different field to have it appear as so:
另外，我想在不同的领域转换类似的东西，让它看起来像这样：
MM/dd/yyyy
月/日/年
Again i know i can get this using Format(), but i would like to to be converted into a DateTime. How would i go about doing this?
我再次知道我可以使用 Format() 获得它，但我想转换为 DateTime。我该怎么做呢？
Any help or suggestions are greatly appreciated.
非常感谢任何帮助或建议。
Thanks.
谢谢。
回答by HansUp
Seems to me the first challenge is reading your custom string as a valid date. In your previous question, you gave this as a sample string stored in your [AlarmStart] field:
在我看来，第一个挑战是将您的自定义字符串读取为有效日期。在您之前的问题中，您将其作为存储在 [AlarmStart] 字段中的示例字符串提供：
Tue Jan 18 10:10:57 2011
The problem is VBA doesn't recognize that string as containing a valid Date/Time value.
问题是 VBA 无法将该字符串识别为包含有效的日期/时间值。
? IsDate("Tue Jan 18 10:10:57 2011")
False
However, if you revise that string (drop the day of the week and move year before time), you can produce a string which VBA recognizes as a valid date.
但是，如果您修改该字符串（删除星期几并在时间之前移动一年），您可以生成一个 VBA 识别为有效日期的字符串。
? IsDate("Jan 18 2011 10:10:57")
True
So you can use a function to split apart the string, rearrange the pieces you need, and return a Date/Time value.
因此，您可以使用函数拆分字符串，重新排列您需要的部分，并返回日期/时间值。
Public Function DateFromCustomString(ByVal pIn As String) As Variant
Dim varPieces As Variant
Dim strNew As String
Dim varReturn As Variant
varPieces = Split(pIn)
strNew = Join(Array(varPieces(1), varPieces(2), varPieces(4), _
varPieces(3)), " ")
If IsDate(strNew) Then
varReturn = CDate(strNew)
Else
varReturn = Null
End If
DateFromCustomString = varReturn
End Function
(The Split() and Join() functions are available starting with Access 2000.)
（Split() 和 Join() 函数从 Access 2000 开始可用。）
And, once you have a Date/Time value from your string, you can use the Format() function to display it however you like.
而且，一旦您从字符串中获得日期/时间值，您就可以使用 Format() 函数以您喜欢的方式显示它。
Format(DateFromCustomString(alarmdet.AlarmStart), "ddd MMM dd yyyy hh:mm:ss") AS AlarmDateTime
Format(DateFromCustomString(alarmdet.AlarmStart), "mm/dd/yyyy") AS AlarmDate
This works as described with English month name abbreviations. I don't know what will happen with Portuguese.
这与英文月份名称缩写的描述相同。我不知道葡萄牙语会发生什么。
回答by Banjoe
You can use a dictionary object to convert the English month abbreviation into a number regardless of your locale. The below function will convert the format you listed above into a valid date/time. You will probably want to write a better error handler.
您可以使用字典对象将英文月份缩写转换为数字，而不管您的语言环境如何。下面的函数会将您上面列出的格式转换为有效的日期/时间。您可能想要编写一个更好的错误处理程序。
Public Function textToDate(text As String) As Date
Dim months As Object
Dim year As String
Dim month As String
Dim day As String
Dim time As Date
On Error GoTo eh
text = Trim(text)
Set months = CreateObject("Scripting.Dictionary")
months.Add "Jan", 1
months.Add "Feb", 2
months.Add "Mar", 3
months.Add "Apr", 4
months.Add "May", 5
months.Add "Jun", 6
months.Add "Jul", 7
months.Add "Aug", 8
months.Add "Sep", 9
months.Add "Oct", 10
months.Add "Nov", 11
months.Add "Dec", 12
year = Right(text, 4)
month = months(Mid(text, 5, 3))
day = Mid(text, 9, 2)
time = CDate(Mid(text, 12, 8))
textToDate = CDate(DateSerial(year, month, day) + time)
Exit Function
eh:
textToDate = #1/1/1900#
End Function
EDIT:If you end up using this solution use HansUp's Split() method rather than my mid(); it's much cleaner and safer.
编辑：如果您最终使用此解决方案，请使用 HansUp 的 Split() 方法而不是我的 mid()；它更清洁、更安全。
回答by Bruno Leite
Use
用
Strings.FormatDateTime("2011-09-01",vbGeneralDate)
