在访问中将文本转换为DateTime [英] Convert a Text to a DateTime in access

问题描述

所以我有一个字段，数据类型是输入数据库的文本.它返回的内容与此类似:

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

我可以使用看起来像这样的Format()来实现:

I can achive this by using Format() which would look like this:

Format(alarmdet.AlarmStart, "ddd MMM dd yyyy hh:mm:ss) AS AlarmDateTime

但是，这一切都很好.我希望能够将此值转换为日期时间.我已经尝试使用CVDate，CDate，DateValue，并且每次返回时都会出现一个错误，声称数据类型不匹配.我该如何将这个确切的字符串转换为日期时间?

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?

注意:

因此，您知道，在英语(美国)语言环境中，我能够使它成功转换，但是我试图在葡萄牙语(葡萄牙)语言环境中使它工作.在此语言环境中，我遇到了不匹配数据类型错误，我认为这与访问方式读取缩短的月份有关.我缺少使它在国际环境下成功工作的东西吗?

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

MM/dd/yyyy

同样，我知道我可以使用Format()来获得它，但是我想将其转换为DateTime.我将如何去做?

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?

任何帮助或建议，我们将不胜感激.

Any help or suggestions are greatly appreciated.

谢谢.

推荐答案

在我看来，第一个挑战是读取自定义字符串作为有效日期.在您先前的问题中，您将此示例字符串存储在了[AlarmStart]中字段:

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:

Tue Jan 18 10:10:57 2011

问题在于VBA无法识别该字符串包含有效的日期/时间值.

The problem is VBA doesn't recognize that string as containing a valid Date/Time value.

? IsDate("Tue Jan 18 10:10:57 2011")
False

但是，如果您修改该字符串(删除星期几并在时间之前移动年份)，则可以生成VBA认为是有效日期的字符串.

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.

? 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

(从Access 2000开始可以使用Split()和Join()函数.)

(The Split() and Join() functions are available starting with Access 2000.)

而且，一旦从字符串中获得了日期/时间值，就可以使用Format()函数以任意方式显示它.

And, once you have a Date/Time value from your string, you can use the Format() function to display it however you like.

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.

这篇关于在访问中将文本转换为DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！