By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,784 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

Date Conversion from Format YYYMMDD

P: 33
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?

Jun 3 '09 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,287
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

P: 33
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.

Jun 3 '09 #3

Expert Mod 15k+
P: 31,709
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

P: 33
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.

Jun 4 '09 #5

Expert Mod 15k+
P: 31,709
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

Post your reply

Sign in to post your reply or Sign up for a free account.