Reformating a date

[Wonderer]

I have a data extract which i need to import into a database however the date field is unregonisable

i.e date = 1021121

this date format for access needs to be 21/11/2002

The data is already in a table how can i change the date?

Answers on postcard......No please any help would be appreciated.

Thanks

Wonderer

 

[Tim K.]

i.e date = 1021121

I understand the last 4 digits, but not the first 3 digits. What 102 stands for? Or it's a typo here?

 

[Wonderer]

102 no its not a typo is the data as comes out of an AS400 database:

102 = 2002 seems strange i know but that's why i have been having problems with it. I really want to use a substring but not sure what the function is in Access normally in Business Objects i would use:

=SubStr(<Date> ,1 ,2) &":"& SubStr(<date> , 3 ,2) &":"& SubStr(<date> , 5 ,2)

Any ideas?

Cheers

Wonderer who is wondering

 

[Wonderer]

Okay i have sorted it so i can have

Mid([scheduled date],2,7)

this gives 021001 How with out creating 4 colums in a query then concatinating the collums can i get round this????

 

[Wonderer]

Not sure if anyone is interested but after playing around i have found the solution

Schedule 2 date: Mid([scheduled date],6,2) & "/" & Mid([scheduled date],4,2) & "/" & Mid([scheduled date],2,2)

Thanks to those of you who have looked at this Post

Wonderer

 

[The_Doc_Man]

The "102" is because the base date of that system must be January 1, 1900 = day 1. Which isn't necessarily bad - Access often uses the same date base.

If the first part is pretty much fixed in position, you could analyze it as taking the last four digits away using the "RIGHT" function,
then splitting them to get months and days separated. Then take out the first part by taking the leftmost digits equal to the length of the string minus 4. Then add back 1900 as the base year, then concatenate a bunch of stuff to form the final string.

Particularly if you still have the chance to see dates in the 20th century from that data source, you need to reconstitute the date based on its reference date.

Unfortunately, the function that would do this as a single event is so totally ugly that you might not like it. But here it is in all its glory, with a note that I'm in an area where default date format is set to 'mm/dd/yyyy'.... your settings might be different.


= CDate( "#" &
Left$( Right$( OddDate, 4), 2 ) & "/" &
Right$( OddDate, 2 ) & "/" &
CStr( 1900 + CInt( Left$( OddDate, Len(OddDate) - 4 ) ) ) & "#" )

First line of that mess is the month
Second line is the day
Third line is the year (if I got the parentheses right)