Converting weird (ISO?) date

[jcarroll01]

I have some values being passed to my query in a date field that I can't figure out how to covert to dd/mm/yyyy.

Here are a couple of the dates

109415 = 04/15/2009
110112 = 11/12/2010
1100208 = 02/08/2010

Anyone have a method for getting this through as a "regular" date for me?

Thanks

 

[ChrisO]

Can you verify: -

110112 = 11/12/2010

Or

1101112 = 11/12/2010

Chris.

 

[namliam]

This format is not consistant... it looks like a AS400 date in which case it would be:
1090415 = 04/15/2009
1101112 = 11/12/2010
1100208 = 02/08/2010

At the very least this one:
110112 = 11/12/2010
Looks very very fishy and doubtfull

If you add 19000000 to the numbers you get
20090415 = 04/15/2009
20101112 = 11/12/2010
20100208 = 02/08/2010

Here you have a standard YYYYMMDD format which you can 'fix' into a real date in access using DateSerial or CDate functions. Something like so
Dateserial (Left(yourValue,4), mid(Yourvalue,5,2), right(yourvalue,2))

 

[Brianwarnock]

Not sure why Chris only picked that one as the first and third also show a lack of consistency.

Brian

Edit Ok namliam has posted while I was thinking.

 

[jcarroll01]

Ugh...sorry. It must be too early in the morning to be looking at this.

You guys were right, I wrote down the values wrong.

Sorry. To solve issues like this it's much harder if you don't get the right info.

 

[namliam]

Ugh...sorry. It must be too early in the morning to be looking at this.

You guys were right, I wrote down the values wrong.

Sorry. To solve issues like this it's much harder if you don't get the right info.

In which case my previous post is completely valid and provides you a solution.

 

[ChrisO]

The inconsistency…

109415 = 04/15/2009
110112 = 11/12/2010
1100208 = 02/08/2010

Remove the Year: -
415
112
0208

Remove the Day: -
4
1
02

Take the Val: -
4
1
2

Only the second is in error, it should be 11.

 

[jcarroll01]

and I thank you very much for your solution and your patience with my initial mistake.

 

[Brianwarnock]

The inconsistency…
.
.

Remove the Day: -
4
1
02

Take the Val: -
4
1
2

Only the second is in error, it should be 11.

What is consistent about day numbers 4 and 02 ?

Still its solved now so its ok.

Brian

 

[ChrisO]

Brian.

You are correct that the first and third are inconsistent; the field length differs.
I did not say they were consistent, I only said that the second was in error.

But that inconsistency is easily fixed by taking the Val of the field, be the length of the field 1 or 2.

I could have said, but didn’t, that they are consistent inasmuch as they share a common fix namely, their value.

4 = 4
02 = 2

So, even with the inconsistency of the first and third, provided all else is consistent, we can remove the first three characters and the last two characters and use Val() on the anything left over.

Chris.