472,143 Members | 1,720 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,143 software developers and data experts.

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?

Jun 3 '09 #1
5 3985
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
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
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
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
32,499 Expert Mod 16PB
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.

Similar topics

3 posts views Thread by praba kar | last post: by
1 post views Thread by Franck | last post: by
3 posts views Thread by josh3006 | last post: by
12 posts views Thread by Assimalyst | last post: by
10 posts views Thread by =?Utf-8?B?TWlrZQ==?= | last post: by
3 posts views Thread by Phil H | last post: by
reply views Thread by Saiars | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.