Convert a Text to a DateTime in access

Question

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

What i would like for it to do is be displayed as something like this:

ddd MMM dd yyyy hh:mm:ss

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

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?

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?

Any help or suggestions are greatly appreciated.

Thanks.

Would you add an example of what the text strings really look like, please? If the MMM values are the same as English month names, this could be part of the problem. You might need to Format the string as a US datetime, then use CDate to save it as a date time, then set your locale to Portugal, and do the formatting one more time to fit Portugese naming for months. — Jennifer S, Aug 31 '11 at 21:47
This is a presentation-level issue, not a SQL issue. Why do you think you need this format in SQL? — David-W-Fenton, Sep 01 '11 at 20:47

score 2 · Accepted Answer · edited May 23 '17 at 11:55

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

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

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

(The Split() and Join() functions are available starting with 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(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.

I've tried using this function which looks like it should work, however i am getting an error when running the query: Undefined function DateFromCustomString I've checked the spelling and made sure the module name it's in is not the same. i've even tried pulling the function from the builder in access which is does, but still get the same error. Any idea why that might be? — James213, Sep 01 '11 at 15:52
Is the function recognized when you try it in the Immediate Window like this? ... `Debug.Print DateFromCustomString("Tue Jan 18 10:10:57 2011")` — HansUp, Sep 01 '11 at 19:13
Yes when i run the above statement it outputs to: `1/18/2011 10:10:57 AM` — James213, Sep 01 '11 at 19:36
Okay, i got it to run, it was just a syntax error. Thanks through! — James213, Sep 01 '11 at 20:23

Banjoe · Answer 2 · 2011-09-01T01:23:56.620

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.

score -1 · Answer 3 · edited Aug 02 '12 at 14:38

-1

Use

Strings.FormatDateTime("2011-09-01",vbGeneralDate)

edited Aug 02 '12 at 14:38

sloth

99,095
21
171
219

answered Sep 01 '11 at 17:50

Bruno Leite

1,403
13
17

Convert a Text to a DateTime in access

3 Answers3

Linked