How can I convert 20050106 text to date?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use Eureka to query a database, then export results to Excel and finally
putting into a table in Access. One of the fields is a date field; however,
it comes over as a text file and I can't change it. The format of the field
is yyyymmdd. How can I convert this to a date file; or, can I somehow show
Date() = yyyymmdd? I want to be able to query this file to show records over
30 days, 60 days and 90 days old.
 
This is probably not the best way, but if the field is
always in the same 8-digit format, you could possibly use
this to convert the text to a date:

Mid([field],5,2) & "/" & Right([field],2) & "/" & Left
([field],4)
 
The format of the field
is yyyymmdd. How can I convert this to a date file; or, can I somehow
show Date() = yyyymmdd?
Click to expand...

Internationally-safe method:

dtNewDateValue = DateSerial(CInt(Mid(strTextDate,1,4)), _
CInt(Mid(strTextDate,5,2)), _
CInt(Mid(strTextDate,7,2)))


converting the other way is even easier:

strTextDate = Format(dtOldDateValue, "yyyymmdd")


Hope that helps


Tim F
 
Did you pursue having Access query the DB directly? Seems like your
taking the scenic route to your destination.

David H
 
The database is a government owned system and I am not aware of a way to
access it directly. I have to run the report and then export it to text or
excel.
 
Thank you for the posts on this thread!
I was trying to figure out how to convert "YYYYMMDD" to "MM/DD/YYYY" via a
query because I can't change the field properties in the table(s).

My Access mentor said "...you must accomplish it through the query...."

Thank you again!
 
I was trying to figure out how to convert "YYYYMMDD" to "MM/DD/YYYY"
via a query because I can't change the field properties in the
table(s).

My Access mentor said "...you must accomplish it through the
query...."
Click to expand...


If you want the value as a DateTime value, then do this:

SELECT ALL
DateSerial(CInt(Mid(MyField,1,4)),
CInt(Mid(MyField,5,2)),
CInt(Mid(MyField,7,2))) AS MyDateValue
FROM MyTable
WHERE LEN(MyField) = 10


If you want the value as a string, but in the other format, then you can
wrap the above in a Format() function. This will produce some odd results
if you have any illegal dates in the column (20060230 for example). An
alternative would be just to do some straight string splicing:

SELECT ALL
Mid(MyField,5,2) & "/" &
Mid(MyField,7,2) & "/" &
Mid(MyField,1,4) AS MyUSDateString
FROM MyTable
WHERE LEN(MyField) = 10

and so on.

Hope that helps


Tim F
 
One way, assuming that you never have nulls in the field

DateValue(Format([TheDateField],"@@@@/@@/@@"))
 
Back
Top