Hi, I'm importing information from my state's vital records agency. The data was given to me in Notepad format. Birth dates are listed as MMDDYY. It appears that when importing, birthdates between 1900 - 1929 are being displayed as 2000, 2010, 2029, etc. Everything later than 1929 is importing correctly. Any ideas on how to get the birthdates between 1900 - 1929 to display correctly?
jstromain
Offline
Last seen: 4 years 7 months ago
Joined: 03/15/2017 - 09:40
Hi Jason and welcome to the site.
You are seeing the problem with the century cutoff. The default for IDEA is 30 so anything under 30 adds a 20 to it and anything over 30 adds a 19 to it.
You can easily change this within IDEA, for V10 select File - Options - and in the middle of the system options you should see date options where you can change a 30 to whatever you need for your file.
Hopefully this will solve your problem.
Thanks
Brian
Thanks. Do I need to redo the import for the change to take place?
Yes you will need to redo the import as the translations is only done on the import, after that you can't change the source data.
Thanks again
Glad I could help.
After making the change I have a new problem. The dates that are supposed to be year 2000 or greater are listed as 19xx.
That is the problem when you only have two digits for the year. How are you finding out that they are in the wrong century? Is there something else in the database that indicates this? If so you might have to add a virtual field and based on the other information either add or subtract a 100 from the year.
The data is information from vital records (birth dates and death dates). For example the birth date will be 01/01/1942 and death will be 01/01/1906 in IDEA
Maybe I can just import the dates in this format MM/DD/YY
Here is what you can do. Create a virtual date field and use the following equation. The equation will compare the birth and death date and if the death date is before the birth date it will add a hundred to it. Hopefully this will fix your problem:
@if(DEATH < BIRTH, @Ctod(@Replace(@str(@day(DEATH), 2, 0) + @str(@Month(DEATH),2, 0) + @str((@Year(DEATH) + 100), 4, 0), " ", "0"), "DDMMYYYY"), DEATH)
Pages