I am working on an import spec from a text field and there is a field with 4 digits only. Is it possible to import this directly as a date field or will I need to import as number or text and then run a conversion to get it to a date field?
Thanks
I am working on an import spec from a text field and there is a field with 4 digits only. Is it possible to import this directly as a date field or will I need to import as number or text and then run a conversion to get it to a date field?
Thanks
How exactly is your date formatted? Is it simply month and date with no year (i.e. MMDD)?
I don't know of any date setting on the Import Specs that will be able to figure out any data less than 6 characters, so you may need to import it as text and then perform your conversion on it.
You could import your data to a temp table first, and then use an Append Query to convert the date and write the date, along with all your other fields, to your final table.
Ok I was afraid of that. It is just the four digits like 1312 - YYMM . I was able to get it to work with the 6 digits like you mentioned but not the 4. Is it best to import as text or a number if I am going to later run some kind of query to convert it to date. I would like it to be a real date field so I imagine I will have to add some kind of standard day of the month and then convert it to a date field.
When having to convert values, I always like to read them in as text. That was you can ensure that things like leading zeroes do not get dropped.
Last edited by JoeM; 06-12-2013 at 08:02 PM. Reason: typo
I found that while trying to read in the 4 digit field as a date it gave me a conversion error and would not actually import any data. Is there any way to force it to bring in the data as a date
I apologize, I made a big typo in my last post.
I meant to say "When having to convert values, I always like to read them in as text..."
instead of "When having to convert values, I always like to read them in as date..."
Thank, you I was thinking it might just be a typo. Could you please explain why? I have several fields I have to do this with and I would like to have a consistent valid reason that I can apply to all of them.
Thanks in advance,
It just makes working with the data easier. If you import them as numbers, leading zeroes will be dropped, where if you import them as text, they will not.
Let's take a look at a few examples of dates in your YYMM format:
1306
0809
1112
0901
If you import them as text, they will all import exactly as seen above. However, if imported as numbers, they will import as follows:
1306
809
1112
901
See now how some entries are three characters long, while others are four? It may make your date conversions a bit trickier if you need to account for both possibilities. However, if you import as text, they are all exactly four characters long, so the conversion should be more straightforward.
Yes, I see. Thank you very much. This kind of thinking can save a lot of trouble later down the line. I appreciate the help and the explanation. I will file this away for future reference.
Your welcome, glad to help!
I am always happy when people want to understand "why", and aren't just looking for quick answers. It shows they really want to learn!