Convert Date 5:44 pm MON FEB 23, 2009 to yyyy/mm/dd 24:00

[kpaull]

I have raw data in .csv files which I import into Access daily. The date field contains "5:44pm MON FEB 23, 2009" or "10:01am TUE FEB 1, 2009" (notice the single and double position day number). I cannot sort using this date. So my options are to modify the .csv files with vba (pre import) or modify it on import, or do a post update query to each table (without my data normalized, I have way too many tables to do this on.... ).

Any takers on how I should approach this, and some sample code?

Help is much appreciated!
Kurtis

 

[raskew]

Hi -

That's a good-un! In playing around with this, testing both CDate() and DateValue() functions, it appears
that in order for either of these functions to convert your text date to datetime data format, it (text date)
must be converted to something Access will read/recognize, e.g.:

x = "5:44pm MON FEB 23, 2009" -- converts to
"FEB 23, 2009 5:44 pm"

y = "10:01am TUE FEB 1, 2009" -- converts to
"FEB 1, 2009 10:01 am"

That process involves:
1) Getting rid of the 3-character weekday ('MON', 'TUE', etc.) designator.
2) Moving the time from the front to the rear of the string.
3) Inserting a space preceeding the am/pm designator.

So, from the debug (immediate) window:

x = "5:44pm MON FEB 23, 2009"
y = "10:01am TUE FEB 1, 2009"

-----------------------------------------------------------------------------------------

? cdate(mid(x, Instr(x, " ") + 5) & " " & left(x, instr(x, " ") -3) & " " & mid(x, instr(x, " ")-2, 2))
2/23/2009 5:44:00 PM

--- and to prove that it's in fact in datetime data format
? cdbl(cdate(mid(x, Instr(x, " ") + 5) & " " & left(x, instr(x, " ") -3) & " " & mid(x, instr(x, " ")-2, 2)))
39867.7388888889

-----------------------------------------------------------------------------------------

? cdate(mid(y, Instr(y, " ") + 5) & " " & left(y, instr(y, " ") -3) & " " & mid(y, instr(y, " ")-2, 2))
2/1/2009 10:01:00 AM
--- and to prove that it's in fact in datetime data format
? cdbl(cdate(mid(y, Instr(y, " ") + 5) & " " & left(y, instr(y, " ") -3) & " " & mid(y, instr(y, " ")-2, 2)))
39845.4173611111

-----------------------------------------------------------------------------------------

Breaking it down a little further:
--- this returns the date
? mid(y, instr(y, " ") + 5)
FEB 1, 2009

--- this returns the time
? left(y, instr(y, " ") -3)
10:01

--- this returns the am/pm designator
? mid(y, instr(y, " ")-2, 2)
am

Insert the necessary spaces and wrap it all in the cDate() function and it returns a true datetime data format.

HTH - Bob

 

[Banana]

I agree with Uncle Gizmo's assessment. Normally we would use Format() but given the inconsistency, this is something we need to fix first.

Off the top of my head:

Private Function PrepFormat(sInput As String) As String 

Dim s As String
dim t as String

s = Mid$(sInput, 16, 2)
Select Case s
    Case "1,", "2,","3,","4,","5,","6,","7,","8,","9,"
        t = Left$(sInput, 16) & "0" & Right$(sInput, Len$(sInput)-16)
End Select

s = Mid$(sInput, 1, 2)

Select case s
    Case "1:", "2:", "3:", "4:", "5:", "6:", "7:", "8:", "9:"
        t = "0" & sInput
End Select

PrepFormat = t

End Function

This function, of course, only works if the formatting is consistent throughout.