Date Conversion from Format YYYMMDD

[servantofone]

I have dates in the format of YYYMMDD that I would like to convert into an actual date. The first digit indicates that the date is after 2000 if it is a 1 and prior to 2000 if it is a 0. So for instance: 1011023 = 10/23/2001.

I can convert it using the following statement in an Access query:

newDate: IIf(Val(Nz([ARN-CREATE-DATE1]))>0,CDate(Mid([ARN-CREATE-DATE1],4,2) & "/" & Mid([ARN-CREATE-DATE1],6,2) & "/" & IIf(Mid([ARN-CREATE-DATE1],1,1)="1","20","19") & Mid([ARN-CREATE-DATE1],2,2)),Null)

Is there a faster or just a cleaner way of converting this date?

-Kyle

[ChipR]

Not sure how much better this is, but I'd probably do:

newDate: DateSerial(
   1900 + 100 * Left([ARN-CREATE-DATE1],1) + Mid([ARN-CREATE-DATE1], 2, 2), 
   Mid([ARN-CREATE-DATE1], 4, 2),
   Right([ARN-CREATE-DATE1], 2))

Do you have nulls, numbers not greater than 0, and dates in your data? If not, that part can possibly be rewritten.

[servantofone]

@ChipR
This is very clever. Thanks for your help. Each field is either null or a date. So I can just evaluate it using an IsNull and if it returns False, use this statement.

Thank you.

-Kyle

[NeoPa]

Try :

newDate: DateSerial(1900+Left([ARN-CREATE-DATE1],3),
                    Mid([ARN-CREATE-DATE1],4,2),
                    Right([ARN-CREATE-DATE1],2))

[servantofone]

@NeoPa
Suddenly my whole world makes more sense. The first three digits of the date indicate how many years PAST 1900 the year is. I never saw that until now!

Thanks for helping me clean this up! This eliminates a serious amount of function calls. You two are the best.

-Kyle

[NeoPa]

@servantofone
Always a pleasure Kyle.

Sometimes just the layout of the display can make the difference to how easy it is to follow.