Date Format : yyyymmdd to mm/dd/yyyy MS Access 2010

I have a filed in my access table in yyyymmdd (20130616) format and  I have to change that to mm/dd/yyyy (06/16/2013). Please Help.There is many number of records there in the table.
gtmathewDallasAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
als315Connect With a Mentor Commented:
You can convert number to date format with Dateserial function:
DateSerial(Left([YourField],4),Mid([YourField],5,2),Right([YourField],2))
0
 
Patrick MatthewsCommented:
What is the data type?

If you are already using the Date data type (best practice), simply go into the table design and change the number format in the column properties.

If it is text, then you could use a query like this:

UPDATE [tablename]
SET [columnname] = Mid([columnname], 5, 2) & "/" & Right([columnname], 2) & "/" & Left([columnname], 4)
WHERE [columnname] Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

Open in new window


Note that you would also want to find any rows that did not have an entry matching the old format:

SELECT *
FROM [tablename]
WHERE Not [columnname] Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

Open in new window


However, I think your current format is much better than mm/dd/yyyy, as there would be no ambiguity over the order of the date parts.

If you are using a numeric data type (Long, Single, or Double), you will not be able to store the /s.
0
 
gtmathewDallasAuthor Commented:
The data type is in number, the reason to converting  yyyymmdd to this mm/dd/yyyy format is  there is another date field in the table with mm/dd/yyyy format.
After making both date fields in to same format- I need to compare those two date fields and find out the record which are not same.
0
 
gtmathewDallasAuthor Commented:
Thank You So Much... It Is Working Good
0
All Courses

From novice to tech pro — start learning today.