On Aug 25, 2016, at 2:00 PM, Eric Naujock wrote: > This database started out life in 4D back in 1985. As a result there is a a > lot of data that dates prior to 6.7. As a result I have a lot of dates that > are stored in 2 digit formats that have never been converted to 4 digit > years. But now I am showing people born in 2032 and having died in 1987. This > kind of messes up the statistics and makes the reporting fail miserably. What > am in need of doing is finding the proper year and being to define it in mass > for all the records in the database so that I have no 2 digit years. > > The code that I used as an example I thought would do it by using > string to convert the date to an ISO format then using date to convert the > string date back to a proper date. The difficulty I am running into is that I > have a ton of 2 digit dates years in the system that I need to get fixed into > 4 digit years and I am looking for a relatively safe an effective way to do > this. Ideally I would like to be able to block the ability to just enter 2 > digit years and require 4 digit years.
On Aug 25, 2016, at 2:00 PM, Peter Bozek wrote: > This will not help. AFAIK, 4D uses three numbers to store date (day, month > and year.) Your problem is some of dates contain year in range 1 - 99 > instead of 1901 - 1999. > > What you need is rather something like (this is short idea, adjust it for > your needs) > > C_DATE($1;$0) > C_LONGINT($year) > > $year:=Year of($1) > Case of > : ($year<15) > $0:=Add to date($1;2000;0;0) > : ($year<100) > $0:=Add to date($1;1900;0;0) > Else > $0:=$1 > End if Peter is right, 4D has always stored dates internally as 3 “fields”. Each “field” is 2 bytes in size or an integer. Hence, maximum year value of 32,732. 4D also puts some constraints on the values allowed to be stored. Minimum year is 100, maximum year 32,767, Maximum month 12 and maximum day 31. But it also allows all 3 of these to be zero. And it won’ t allow you to store a date like February, 31 2016 which cannot exist in real life. You can verify this in the debugger with code like this: C_DATE($date_d) $date_d:=Add to date(!00-00-00!;2016;2;31) $date_d:=!2016-02-31! Notice the debugger shows $date_d to be: March, 2 2016 I would do a little analysis of these bogus dates using the “Year of” function on the date field. If it returns values like 87 or 32 instead of 1987 or 2032 then you might be able to use Peter’s code above to fix them. The current documentation says it doesn’t allow years below 100. http://doc.4d.com/4Dv15/4D/15.1/Data-Types.300-2685800.en.html But just to be sure, and I always like to be sure, I did a quick test and found that the “Add to date” function appears to bypass documentation. You can store a date with a year of 10. This code will store a date with a year of 16. So the documentation is wrong about minimum year allowed! C_DATE($date_d) $date_d:=Add to date(Current date;-2000;0;0) $year:=Year of($date_d) If year is returning 4 digit years you may have to be creative and try to logically fix the dates. Example: if the year of the birth date or death date is greater than 2016, then you can be certain the you need to subtract 100 years to fix it. If the death year is before the birth year — like your example 1987 and 2032 — then you can logically assume the birth year needs 100 subtracted from it. That’s all I can offer on how to clean up your data. Spend some time with “Add to date” and the “Year of” functions and see what values you are getting. That may help you figure out a logical way of fixing these bogus dates. One last “good programming practice” thing is for any birthdate fields on forms always set the display format to show 4 digit years and set the entry filter to require entering 4 digit years. This can make obvious data entry errors immediately seen and corrected. Tim ******************************************** Tim Nevels Innovative Solutions 785-749-3444 [email protected] ******************************************** ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
