Importing dates

jstromain

Forums

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?

Brian Element Wed, 03/15/2017 - 10:29

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

jstromain Thu, 03/23/2017 - 09:55

After making the change I have a new problem. The dates that are supposed to be year 2000 or greater are listed as 19xx.

Brian Element Thu, 03/23/2017 - 10:04

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.

Brian Element Thu, 03/23/2017 - 10:41

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)