• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

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.
0
pgobos
Asked:
pgobos
  • 4
  • 4
  • 3
  • +1
1 Solution
 
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
 
Nick67Commented:
select CDate([WhateverYourFieldIs]) as MyFormattedDate from tblWhatever;
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
OP_ZaharinCommented:
- 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
 
pgobosAuthor Commented:
thanks that did it
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now