从MS Access中的字符串中提取/转换日期 [英] Extract/convert date from string in MS Access

问题描述

我正在尝试使用以下模式从字符串中提取日期/时间，并将其转换为Access中的日期类型.

I'm trying to extract date/times from strings with the following patterns and convert them to date types in Access.

1. "2012年4月8日21:26:49"

1. "08-Apr-2012 21:26:49"

"...由SMITH，MD，JOHN(123)于2012年4月2日11:11:01确认;"

"...Confirmed by SMITH, MD, JOHN (123) on 4/2/2012 11:11:01 AM;"

任何人都可以帮忙吗?

推荐答案

将此功能添加到VBA模块中:

Add this function to a VBA module:

' ----------------------------------------------------------------------'
' Return a Date object or Null if no date could be extracted            '
' ----------------------------------------------------------------------'
Public Function ExtractDate(value As Variant) As Variant
    If IsNull(value) Then
        ExtractDate = Null
        Exit Function
    End If

    ' Using a static, we avoid re-creating the same regex object for every call '
    Static regex As Object
    ' Initialise the Regex object '
    If regex Is Nothing Then
        Set regex = CreateObject("vbscript.regexp")
        With regex
            .Global = True
            .IgnoreCase = True
            .MultiLine = True
            .pattern = "(\d+\/\d+/\d+\s+\d+:\d+:\d+\s+\w+|\d+-\w+-\d+\s+\d+:\d+:\d+)"
        End With
    End If
    ' Test the value against the pattern '
    Dim matches As Object
    Set matches = regex.Execute(value)
    If matches.count > 0 Then
        ' Convert the match to a Date if we can '
        ExtractDate = CDate(matches(0).value)
    Else
        ' No match found, jsut return Null '
        ExtractDate = Null
    End If
End Function

然后像这样在查询中使用它:

And then use it like this, for instance in a query:

SELECT ID, LogData, ExtractDate(LogData) as LogDate
FROM   MyLog

确保您检查返回的日期格式正确，并且对您有意义. CDate()根据您的语言环境以不同的方式解释日期字符串.

Make sure you check that hte dates returned are in the proper format and make sense to you. CDate() interprets the date string in different ways depending on your locale.

如果未获得理想的结果，则需要修改代码以分隔日期的各个部分，并使用DateSerial()进行重建.

If you're not getting the desired result, you will need to modify the code to separate the individual components of the date and rebuild them using DateSerial() for instance.

这篇关于从MS Access中的字符串中提取/转换日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！