How can I convert 20050106 text to date?

[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.

 

[abbarition]

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)

 

[Tim Ferguson]

The format of the field
is yyyymmdd. How can I convert this to a date file; or, can I somehow
show Date() = yyyymmdd?

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

 

[John Spencer (MVP)]

Another option

DateValue(Format(DateString,"@@@@/@@/@@"))

 

[David C. Holley]

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

David H

 

[Guest]

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.

 

[Guest]

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!

 

[Tim Ferguson]

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...."

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

 

[John Spencer]

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

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