i have a table that has the wrong year in a date field 1

[Ernesto_Paez]

Hi everyone
i have a table and i opened it in vfp 9.0
i have a field named "day" and this is a date field type
some values under the "day" are appearing as 02/26/1918 and i know the year suppose to be 2018
can anyone please pointing how can i scan this table and where ever i find 1917 or 1918 to be able to replace the year accordingly to 2017 and 2018 ?
thanks in advance

 

[Mike Lewis]

Try something like this:

[tt]SELECT TheTable
REPLACE Day WITH GOMONTH(Day, 1200) FOR YEAR(Day) = 1917 OR YEAR(Day) = 1918[/tt]

Note that the above answers the exact question that you asked. It will update dates in 1917 and 1918 to 2017 and 2018. But I suspect you also want to know how to prevent this situation from happening in the first place. If so, read the Help topic on SET STRICTDATE and SET CENTURY ... ROLLOVER.

By the way, Day is not a good choice for a field name, as it is also the name of a built-in function.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

There's not much more to say. Knowing there is a YEAR() and GOMONTH functions you can also simply find out any dates outside of a range of valid years for your data, if you search for it. Also Date() heps as reference for today, so you could find any future dates not valid, too:

Everything outside the last 20 years up to today:

SELECT * FROM TheTable Where Day<GOMONTH(DATE(),-240) OR Day>Date()

I once migrated data, that had a long history starting in Foxpro DOS tables, where dates had century errors even more off, going back to the first century. And though DOS is old, it's not that old.

Bye, Olaf.

 

[Ernesto_Paez]

Tnanks a lot Mike, yes correct day it is not good name for that field, the problem is that this field name it is all over a bunch of report someone before me defined like that, so a lot of work to do to replace everywhere that field named DAY
will look into those commands
THANKS A LOT

 

[Ernesto_Paez]

Great olaf , thanks so much, that is a good trick

 

[dbMark]

It appears these dates were entered or added in shortened form such as "18" rather than 2018. The SET CENTURY setting should avoid defaults to 19XX that would be outside the usual range. However, don't force every 2 digit year to 20XX, there may be times you want to enter an age or event and if the person is 100 years old you would want 1918 rather than 2018. You may ponder whether you want to enforce 4 digit year entries on at least some of your data entry points.