need to replace the year in a Fdos table 2

[Ernesto_Paez]

Hi,
i have table with "date field" named "sch_date" because of the problem of year 2000, on fdos table the field mentioned does not get the proper year 2018 instead is getting in table 1918, i am wondering what will be the easy thing to replace all those year incorrect by the proper 2018 ?
Thanks in advance

 

[Mike Lewis]

Well, the first thing you need to decide is a cut-off year. For example, if the date in your table is 1 January 1995, then you probably want to keep the year as 1995, not make it 2095. But if the date is 1 January 1905, you probably want to make it 2005.

So, let's say you decide the cut-off year is 50. You could do something like this:

[tt]USE TheTable
REPLACE ALL Sch_Date with GOMONTH(Sch_Date, 1200) FOR MOD(YEAR(Sch_date), 100) < 50[/tt]

Alternatively, you might want to say that if the year is less than the current year, change the 19 to 20. That would be appropriate if you were working with past dates, such as birthdates. In that case, you could do this:

[tt]USE TheTable
REPLACE ALL Sch_Date with GOMONTH(Sch_Date, 1200) FOR MOD(YEAR(Sch_date), 100) < MOD(YEAR(DATE()), 100)[/tt]

I am assuming that, although this is an FPDos table, you are actually working in VFP. The above code won't work in Fox 2.x or below, because there would be no GOMONTH() function. If you are still using an older version, then post the question in the relevant forum here on Tek Tips.

NOTE: I haven't tested the above code. Since it makes global changes to your table, be sure to take a backup before you start.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Ernesto_Paez]

Hi Mike
i don't want to do the replace command using vfp, in just in case it damage the table and maybe cannot be read from the old still Fdos application.
i rather to do it still from fdos application or from the command window in fdos but i tried different ways and i am not able too, unless i convert the date field(sch_date) into character and so son, so it is tedious, so i am looking for a short way if possible
thanks in advance

 

[Mike Lewis]

I understand what you are saying about making the file unreadable in FPDos. But that's easy to avoid.

First, make a copy of the file. Open that copy in VFP, and run the code I showed you on the copy. Then do this:

SELECT TheCopy
COPY TO NewCopy TYPE FOX2X

This will create a file named NewCopy that will be usable in your Dos version. Just rename it to match the name of the original file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Hold on a moment. I've just been looking at my old Foxpro docs. It seems that GOMONTH() was available in ver. 2.x after all, as were the other function I showed in my code. So you should be able to use the REPLACE command as I showed you in your old version.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Ernesto_Paez]

Mike yes the Fdos has that command
for example if have a few records with values in that date field sch_date as 1918 and other records with 1920

how can i make the above line command to make sure 1918 is replaced by 2018 as well 1920 be replaced by 2018 ?

sorry i don't get very well the concept of this command
Thanks a lot

 

[Mike Lewis]

Just type the first commands I showed you. Type them in the command window, then press Enter after each of the two lines. That's all you have to do.

Here are the commands again:

[tt]USE TheTable
REPLACE ALL Sch_Date with GOMONTH(Sch_Date, 1200) FOR MOD(YEAR(Sch_date), 100) < 50[/tt]

If that doesn't work, tell me exactly what happened, for example, if you saw an error message.

Of course, you should place the actual name of your table in place of TheTable.

And remember what I said earlier about taking a backup. Or, better still, make a copy of your table and experiment on that copy rather than the live file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Why not simply FOR YEAR(Sch_date)<1950?

I agree to the procedures Mike suggests, backup, then try, you'll easily be able to go back to the file copy.

Bye, Olaf.

 

[Ernesto_Paez]

Hi Mike,
When i run this
USE mytable
REPLACE Sch_Date with GOMONTH(Sch_Date, 1200) FOR MOD(YEAR(Sch_date), 100) < 50 && ONLY DOING FOR ONE RECORD TO CHECK
so i removed the "ALL" after the "REPLACE"
i am getting error "TOO FEWw ARGUMENTS"
Thanks again

 

[Mike Lewis]

Sometimes you get so close to a problem that you miss something obvious. In this case, I should have done what Olaf suggested, that is, test for [tt]YEAR YEAR(Sch_date)<1950[/tt] rather than messing around with MOD().

Also, you don't need the ALL clause. The presence of the FOR clause is enough to define the scope of the replacement.

So, here is an improved version:

[tt]REPLACE Sch_Date with GOMONTH(Sch_Date, 1200) FOR YEAR(Sch_date) < 1950[/tt]

Try that and see if it works.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Ernesto_Paez]

Guys this is working as expected but as you know i am always learning, so can anyone explain the
" FOR YEAR(Sch_date) < 1950" actually what is doing ?
i suppose that will do that while the sch_date actual value is less than 1950

sorry but if i don't ask, i won't learn.
Thanks a lot to both

 

[Mike Lewis]

Yes, of course you are right to ask.

With the REPLACE command, a FOR clause says that the command will only operate on those records that meet the condition. If you have 100 records, and 20 of them meet the condition, then the replacement will work only with those 20.

In this case, the condition is [tt]FOR YEAR(Sch_date) < 1950[/tt]. Sch_date is the date you are interested in. The YEAR() function separates out the year part of that date. So if the date is 14 Feb 2018, then the year is 2018. So when you say [tt]FOR YEAR(Sch_date) < 1950[/tt], only those records whose year is earlier than 1950 will get replaced.

So, if the original date was 1918, 1925, 1939 or 1949 (to take a few random examples), then they will be changed to 2018, 2025, ... etc. But if the original dates were 1975, 1984, 1999, 2001, etc., they will remain the same.

I hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Ernesto_Paez]

Thanks Mike, what a great explanation, well explained, i understood correctly, sometimes people explain things but sometimes i don't get whole view, thanks again for taking the time, i guess you can be a professor, as you explain very well detailed.

 

[Olaf Doschke]

Makes me wonder why you didn't ask for the MOD logic, as YEAR(somedate) is so much easier to understand, even not knowing the function. Mike explained it already, what do you think YEAR(somedate) returns? Just read it in the way you read functions using Functionname "of" parameter: YEAR of somedate.

You have to make some decision about the rollover year, you may not want dates past 1919 to roll over to 2019 and later, as it's only about dates up to today, but think a few months or years into the future. Think about earliest dates of your application and 1950 is likely a choice not overlapping with past and future usage.

You may also look into that setting of VFP: SET CENTURY It has a ROLLOVER year option. Also SET CENTURY ON and users can't enter dates without the century portion of the year and you don't get dates misplaced by a whole century.

I think FDOS also already has this setting, it was important way back then to save 2 bytes per date, but it also should have been possible to store full dates in the time FDOS was a recent platform.

Bye, Olaf.

 

[Ernesto_Paez]

Hi Olaf
sorry, that i excluded you from the above post, you also explain things very well, yes i have "cent on" as well but i need to find out why this particular field is getting the whole date including the year but instead of 2018 it is appearing in the Fdos table as 1918.
Well respect to Mod() i understood that it is remainder result of the dividend/divisor but i think your approach of having "FOR YEAR(Sch_date) < 1950" was an easy one.

besides that when using the Mod() tends to confuse me as Mod(year(date()),100)= 18, however Mod(year(date()),4)= 2
i don't see in FDOS the ROLLOVER option.
Thanks a lot

 

[Olaf Doschke]

I once (1999) migrated old data even having dates with 0 or 1 as their century, so I had dates like 93, really year 93 or year 195, when both times 1993 or 1995 was meant by the context of a date current at the usage of the software.

I think there is a bug in date storage of old FP versions. The data we pulled into a VFP6 database back then was in free FPW 2.6 tables and centuries was one of the data quality problems to solve during the data migration. At least the old data was not DOS. I had no problem in VFP6 and later with such century glitches.

It might also have to do with date entry without the century. Typically current century then is added, but in old software instead of this automatism, the century might be automatically set to 19, as this software was never thought to be used past 2000. Then you naturally have this behavior, but it would be a repeatable error, even on any date, not just some.

Perhaps the screens were adapted, but some of them are still lurking unchanged in that aspect.

Bye, Olaf.

 

[Olaf Doschke]

>Mod() tends to confuse me as Mod(year(date()),100)= 18, however Mod(year(date()),4)= 2

It shouldn't confuse you. You get 1918/100 = 19 rest 18 and 18 is the modulo, you get 1918/4=479 rest 2 and 2 is the modulo.

The rest of a division can only be lower than by what you divide. Mod(x,100) can range from 0 to 99, Mod(y,4) only from 0 to 3. Finding lowest significant digits in a decimal you mod by 10^numberofdigits, eg 10^2=100 for the two least significant digits (values 1s and 10s).

Anyway, since your dates should all be in the range 1950...2018, perhaps even more restricted, it's unnecessary to remove the centuries, you can right away simply check for lower than anything it could be, that lower limit could also be 1980, 1990, I don't know how old the data is. Mod(year,100) could even become >50 again for dates in 1851 to 1899. That property of MOD isn't of interest, as those dates also would be wrong for sure.

Bye, Olaf.