472,143 Members | 1,720 Online

# Date Conversion from Format YYYMMDD

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:

Expand|Select|Wrap|Line Numbers
1. 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
Jun 3 '09 #1
5 3985
ChipR
1,287 Expert 1GB
Not sure how much better this is, but I'd probably do:
Expand|Select|Wrap|Line Numbers
1. newDate: DateSerial(
2.    1900 + 100 * Left([ARN-CREATE-DATE1],1) + Mid([ARN-CREATE-DATE1], 2, 2),
3.    Mid([ARN-CREATE-DATE1], 4, 2),
4.    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.
Jun 3 '09 #2
@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
Jun 3 '09 #3
NeoPa
32,499 Expert Mod 16PB
Try :
Expand|Select|Wrap|Line Numbers
1. newDate: DateSerial(1900+Left([ARN-CREATE-DATE1],3),
2.                     Mid([ARN-CREATE-DATE1],4,2),
3.                     Right([ARN-CREATE-DATE1],2))
Jun 4 '09 #4
@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
Jun 4 '09 #5
NeoPa
32,499 Expert Mod 16PB
@servantofone
Always a pleasure Kyle.

Sometimes just the layout of the display can make the difference to how easy it is to follow.
Jun 4 '09 #6