asked on

MS Access string to date conversion

Hello,

I have a table in MS Access. 2 of the columns are 1)Date and 2)Date/Time. The format of them are as follows:

1. Date - An 8 digit string e.g 20060131 for the 31st Jan 2006.
2. Date/Time - An 14 digit string e.g 20060131203059 for the 31st Jan 2006 at 20:30:59

I want to make a new table using a make table query and convert the 2 columns into the following format:

1. Date - From yyyymmdd to dd/mm/yyyy
2. Date/Time - From yyyymmddhhmmss to dd/mm/yyyy hh:mm:ss

Is there any way of doing this within the make table query and if so how.

Your help is much appreciated.

Regards

Ritesh

jjafferr

you don't need a new table,
open your existing table, click on your date field, then in the properties at the bottom,
in the Format have this:
dd/mm/yyyy

for Date/Time have this in the Format:
dd/mm/yyyy hh:nn:ss

jaffer

jjafferr

OR you can just copy your existing Table to another, using:
click on the Table,
File > save as

then do these settings in the new Table

jadedata

string manipulation and dateconvert

8chr
x = cdate(mid(yyyymmdd,5,2) & "/" & right(yyyymmdd,2) & "/" & left(yyyymmdd,4))

14chr
x = cdate(mid(mid(yyyymmddhhmmss,5,2) & "/" & mid(yyyymmddhhmmss,7,2) & "/" & left(yyyymmddhhmmss,4) & " " & mid(yyyymmddhhmmss,9,2) & ":" & mid(yyyymmddhhmmss,11,2) & ":" & right(yyyymmddhhmmss,2)))

make sure the syntax chops them up in the correct spots

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

Well - you could set the format property for the given field in a query, but you shouldn't be expecting queries to provide predictable UI information anyway.
It's not what they're for.

jadedata

i would be converting the values into new fields with a query into the same table for side-by-side comparison before going live with the data. i don't see why you need a new table. there must be more fields than those two in the table now.