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

date format - MS Access 2010

My date in access table is in ddmmyyyy (29052013) format, how can i convert that in to mm/dd/yyyy format? I have around 600 rows in the table, Please help
0
gtmathewDallas
Asked:
gtmathewDallas
  • 4
  • 3
1 Solution
 
peter57rCommented:
What is the datatype of your date field?
0
 
gtmathewDallasAuthor Commented:
Its in number
0
 
peter57rCommented:
Then you need to create a new field in your table which is a datetime field.  You can set the format of that field to whatever you want to see displayed.

You then need to use an update query to convert the existing value to a date and place it in the new field.  That will involve quite a long expression.

Your query sql will be your version of this..

Update tablename set newfieldname =
dateserial(right(cstr(fieldname),4), mid(format(fieldname, "00000000"),3,2), left(format(fieldname, "00000000"),2))
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gtmathewDallasAuthor Commented:
Thanks A Lot For This Perfect Answer.. It is working...
0
 
gtmathewDallasAuthor Commented:
I am sorry it worked but not in the desired format.. actually the date was in yyyymmdd (20130616) format and  I have to change to mm/dd/yyyy (06/16/2013). I tried with some changes in above code but not getting the result.
0
 
peter57rCommented:
The example you gave in your original Q does not look like yyyymmdd.

Update tablename set newfieldname =
dateserial(left(format(fieldname, "00000000"),4), mid(format(fieldname, "00000000"),3,2), right(format(fieldname, "00000000"),2))
0
 
gtmathewDallasAuthor Commented:
Yes It is working fine - I changed mid(format(fieldname, "00000000"),3,2) to mid(format(fieldname, "00000000"),5,2)
Thank You..
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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