Modifying numerous records - year part of the date

[Dabase]

Hi,

I am trying to modify the year part of the date on various fields in a FP table. I am trying to add 1000 to the year part of all records that fall below 1900. I am trying to use the replace all function, but can't get the syntax right.

Your help would be much appreciated.


Thanks
Dabase

 

[Mike Gagnon]

Can you post your code so we can see where the problem is?



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Dabase]

Apologies:

I meant to type: "I am trying to add 1000 to the year part of all records that fall below 1000".

Thanks
Dabase

 

[Dabase]

My code:

REPLACE ALL Year(dbirth) WITH (Year(dbirth) + {^1000}) FOR (Year(dbirth) < {^1000})

The Error I am getting:

Command contains unrecognised phrase/keyword

Thanks
Dabase

 

[mm0000]

Use the gomonth function:

gomonth(date,numberofmonths)

eg:

x=gomonth(date(),1200) &&x = date + 100 years

 

[AirCon]

Dabase,

Just to add to mm0000 suggestion, try this:

REPLACE ALL dbirth ;
   WITH gomonth(dbirth, 1200) ;
   FOR (Year(dbirth) < 1000)

-- AirCon --

 

[Mike Gagnon]

REPLACE ALL Year(dbirth) WITH (Year(dbirth) + {^1000}) FOR (Year(dbirth) < {^1000})

You cannot replace just the year portion of a date field, you have to replace the whole date.

Try

REPLACE ALL Year(dbirth) WITH GOMONTH(dbirth,12000)
 FOR (Year(dbirth) < {^1000})

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Gagnon]

Beware of typos.

REPLACE ALL dbirth WITH GOMONTH(dbirth,12000)
 FOR (Year(dbirth) < {^1000})

P.S. this will add 1000 years to the date, as oppose to Aircon's suggestion is the same as this, but will only add 100 years.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Dabase]

Thanks for all the help on this one. Just a shame that you cannot modify just the year.

Thanks
Dabase

 

[dbMark]

Date fields are stored in an 8-digit format such as "20040406" inside the table. While you could theoretically use FOPEN() and locate the proper fields and change just the year there, you'd have to be sure of the offset for the table header and be sure you changed only the correct part of each record as you stepped through the table. Very awkward, time-consuming to code, and at high risk of a terrible data-corruption accident. It is faster and safer to use the methods such as GOMONTH() described above. Sorry, but you can't replace part of a field, you have to replace the whole field even if changing only part of it.

dbMark