Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3498
  • Last Modified:

Convert text to date in MS Access query

I get a file that contains date and time in a text field.  The system owners are unwilling to change the format of the output.   I need to convert it so it can be appended to a date field. I'm pretty good at parsing data, but this one is giving me trouble.  The idea is to write an Access query against the file and convert the "date" to a format that can be successfully appended into a date field in an Access table.  Here this the format:

Wed Jan 12 05:34:55 PST 2011

Thanks
0
upsfa
Asked:
upsfa
  • 2
  • 2
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this


cdate(mid("Wed Jan 12 05:34:55 PST 2011",5,6) &", " & Right("Wed Jan 12 05:34:55 PST 2011",4))
0
 
upsfaAuthor Commented:
Thanks, that gets date, but I am looking for date and time.  Also, not sure about how to address time zone.
0
 
als315Commented:
If you need time also, you can use this function:
Function Txt2Date(Dt As Variant) As Variant
Dim A() As String, D As Date
Txt2Date = Null
If IsNull(Dt) Then Exit Function
A = Split(Dt, " ")
Txt2Date = DateValue(A(1) & " " & A(2) & " " & A(5)) + TimeValue(A(3))
End Function

Open in new window

In function you can also add time shift for time zone if you need
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Rey Obrero (Capricorn1)Commented:
oh..

try this


cdate(mid("Wed Jan 12 05:34:55 PST 2011",5,6) &", " & Right("Wed Jan 12 05:34:55 PST 2011",4) + mid("Wed Jan 12 05:34:55 PST 2011",11,8))
0
 
als315Commented:
Try this sample with Time zone converted to CST
DBdateconv.accdb
0
 
upsfaAuthor Commented:
Thank you both.  The function by als315 is more comprehensive, but Rey's formula is what the user has decided to use.  Meets thier need and is easier to implement.

Thanks again.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now