convert text string to date in access

Database: microsoft access

I have a text filed containing the date in the following format:
Wed May 06 03:13:54 PDT 2009

I would like to convert the text string into a usable date filed.
pgobosAsked:
Who is Participating?
 
OP_ZaharinConnect With a Mentor Commented:
- or you can add CDate() function to it:

SELECT CDate(format(Mid(Dates, 9,2) + '/' + Mid(Dates, 5,3) + '/' + right(Dates,4),"dd/mm/yyyy")) FROM tblName;

Open in new window

0
 
käµfm³d 👽Commented:
Have you tried CDate()?
Dim src As String = "Wed May 06 03:13:54 PDT 2009"
Dim dt As Date = CDate(src)

Open in new window

0
 
pgobosAuthor Commented:
That's what I am looking for the syntax to use in a select statement that will parse the text data and convert it into a usable date format
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Nick67Commented:
select CDate([WhateverYourFieldIs]) as MyFormattedDate from tblWhatever;
0
 
pgobosAuthor Commented:
That returned #Error in MyFormattedDate
0
 
OP_ZaharinCommented:
hi pqobos,
- if the length of the Day (Mon, Tues, Wed) and Month (Jan, Feb, Mac) is fixed, we can use Mid and Right to extract the Day-Month-Year from the text field. then format it to date (any date format you required):
- assuming i have tblName with Dates column containing the text:

SELECT format(Mid(Dates, 9,2) + '/' + Mid(Dates, 5,3) + '/' + right(Dates,4),"dd/mm/yyyy") FROM tblName;

Open in new window

0
 
OP_ZaharinCommented:
- kindly apologies for my explanation. it should be like this:

"if the length for the Day (01, 02, 03) and Month (Jan, Feb, Mac) is fixed, we can use Mid and Right to extract the Day-Month-Year from the text field. then format it to date (any date format you required):"
0
 
Nick67Commented:
How much change does the format undergo?
It hates the day
It hates PDT

We can build a wrapper function in VBA to rip that string apart and make it 6-May-2009 03:13:54 which is a date.
How much does it change though, in terms of number of characters?
0
 
pgobosAuthor Commented:
OP_Zaharin your solution works  Thu Sep 03 08:46:40 PDT 2009  returned 09/03/2009 I changed to mm/dd/yyyy.  Can I add some formatting so the new field is formatted as a date filed, presently it returns 09/03/2009 into what appears to be a text field
0
 
OP_ZaharinCommented:
pgbos,
- great it works!
- actually the codes already format the text to a date when we use the "format(,"dd/mm/yyyy")".
- what you need to do next is to create a field with Date datatype and insert the formatted date into that field.
0
 
Nick67Commented:
Put this function in a module

Public Function TransformString(BadField as string) as date
dim myPieces() as string 'array starts at 0
dim FinalString as string
' "Wed May 06 03:13:54 PDT 2009"
myPieces()=split(BadField," ")
'do nothing with 0, it's the day
'1 is month
'2 is day
'5 is year
FinalString = myPieces(2) & "-" & myPieces(1) & "-" & myPieces(5)
TransformString = CDate(FinalString)

end function

In your query

select TransformString([BadField]) as MyFormattedDate from tblWhatever;)
0
 
pgobosAuthor Commented:
thanks that did it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.