Date format problem convert from number to date

[DrSmyth]

Hi, I've inherited a database (someone has given me a whole bunch of data that they've stuck into Access for me). The problem I've got is that all the dates (there are seven fields in each of 13 tables) are shown in YYYYMMDD format.

Because of this they are stored as numbers (e.g. 21/11/2005 is shown as 20051121) and i haven't been able to turn them back to dates.

Can anybody help?

 

[stephenk1973]

Update based on the stripping and trimming your field.

UPDATE tbl
SET tbl.Field= DateValue(Right([field],2) & "-" & Right((Left([field],6)),2) & "-" & Left([field],4));

May be worth adding a formatted date column to your table and updating that field rather than potentially mucking with your current data, rename and replace one your happy.

Hope this helps

 

[PHV]

In table design view create a new DateTime field.
Then you may launch an update query like this (SQL code):
UPDATE yourTable
SET [new date field] = DateSerial(Left([old date field],4), Mid([old date field],5,2), Right([old date field],2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DrSmyth]

Cheers gents works like a dream...