formatting a date in a text field

[dchristo]

I need to format a date that is in a text field - ex. 20081108 needs to be
11/08/2008 - how do I do this?

p.s. I cannot change the field from a text to a date - it deletes all the info

 

[Jackie L]

First, to change the text field to a date format:

Format(DateSerial(Left([DateField],4),Mid([DateField],5,2),Right([DateField],2),"mm/dd/yyyy")

It would probably be best to change the field to date type for programming
and reporting down the line. To do this (first backup your data) then add a
new field to your table (with a type of date). Do an update query to
populate the new field with an expression like shown above. After the new
field is populated, change the field names so that your new field is the one
used in the program and the old one is a different name (if you still need it
in your data) or is deleted after data is verified.

Hope this helps,
Jackie

 

[John W. Vinson]

I need to format a date that is in a text field - ex. 20081108 needs to be
11/08/2008 - how do I do this?

p.s. I cannot change the field from a text to a date - it deletes all the info

That's because, even though the date parser is pretty clever, it will not
recognize an 8-digit number as a date.

I'd suggest adding a Date/Time field to the table, and then running an update
query updating it to

CDate(Format([textdate], "@@@@-@@-@@"))

Check to be sure that you're getting valid and correct dates, and then use the
new date/time field instead of the text field.

If this will be a recurring import of some sort, you may need to make the same
change as part of the import process, or even use the CDate(()) expression as
a calculated field in a query.

 

[Ken Sheridan]

To change the data type of the column to DateTime first run the following
'update' query:

UPDATE YourTable
SET YourDate = FORMAT(YourDate,"0000-00-00");

which converts the string into the ISO standard date notation of yyyy-mm-dd.

Then run this one to change the column's data type to DateTime:

ALTER TABLE YourTable
ALTER COLUMN YourDate DATETIME;

You'll need to change the table and column names in the SQL to the real ones
of course. It goes without saying that the table should be backed up first
before doing the above.

To simply format the existing text string you can use:

FORMAT(FORMAT(YourDate,"0000-00-00"),"mm/dd/yyyy")

Ken Sheridan
Stafford, England

 

[Duane]

In Access 2003, an import spec could be set to recognize 8-digit fields as
dates. Interestingly, Excel 2007 still can import them correctly. I wonder
why Access 2007 forgot how?

I need to format a date that is in a text field - ex. 20081108 needs to be
11/08/2008 - how do I do this?

p.s. I cannot change the field from a text to a date - it deletes all the info

That's because, even though the date parser is pretty clever, it will not
recognize an 8-digit number as a date.

I'd suggest adding a Date/Time field to the table, and then running an update
query updating it to

CDate(Format([textdate], "@@@@-@@-@@"))

Check to be sure that you're getting valid and correct dates, and then use the
new date/time field instead of the text field.

If this will be a recurring import of some sort, you may need to make the same
change as part of the import process, or even use the CDate(()) expression as
a calculated field in a query.