FOX 2.6 Changing the year of a date field Only 2

[jlg13]

Hi Fox experts.

I hope this thread finds you healthy from COVID-19!

I need a REPLACE statement that changes the 4 digit year if certain conditions are TRUE.

my table is "Contract"
my date field "pymt_ddate"

Essentially I am looking for the following condition across thousands of records...

If contract.pymt_ddate is greater than 2010 change the year to 2120 (EXCEPT if the year is 2099, don't change it)

Many thanks,
Joe

 

[Scott24x7]

JLG13,

SELECT CONTRACT
GO TOP
DO WHILE NOT EOF()
   IF YEAR(CONTRACT.PYMT_DDATE) > 2010 .AND. YEAR(CONTRACT.PYMT_DDATE) <> 2099
      REPLACE CONTRACT.PYMT_DDATE WITH CTOD("2120"+SUBSTR(DTOC(CONTRACT.PYMT_DDATE),5,3)+RIGHT(DTOC(CONTRACT.PYMT_DDATE),3))
   ENDIF
   SELECT CONTRACT
   SKIP
ENDDO

This is a bit of an "old school" way, but its fast enough and very effective.

 

[jlg13]

Thanks Scott. I will give this a go.

 

[TamarGranor]

No need for a loop here. Also, using CTOD() and DTOC() makes you dependent on the local date settings;

[pre]REPLACE PYMT_DDATE WITH GOMONTH(PYMT_DDATE, 2120-YEAR(PYMT_DDATE) * 12) ;
FOR YEAR(PYMT_DDATE) > 2010 AND YEAR(PYMT_DDATE) <> 2099 ;
IN Contract[/pre]

Tamar

 

[jlg13]

Hi Tamar,

Getting an error in Fox 2.6

REPLACE PYMT_DDATE WITH GOMONTH(PYMT_DDATE, 2120-YEAR(PYMT_DDATE) * 12) ;
FOR YEAR(PYMT_DDATE) > 2010 AND YEAR(PYMT_DDATE) <> 2099 ;
IN Contract



Can I execute your Replace code at the command prompt?

 

[jlg13]

Let me simply this a bit.

I am looking to run a REPLACE ALL at the command prompt.

If the year in date field contract.pymt_ddate = 2150 change it to 2050

Thanks,
Joe

 

[Scott24x7]

jlg13
What is the error you are getting? (What does the error say exactly?)


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Mike Lewis]

Can I execute your Replace code at the command prompt?

Yes, provided the relevant table is open and selected.

If the year in date field contract.pymt_ddate = 2150 change it to 2050

REPLACE ALL pymt_ddate WITH GOMONTH(pymt_ddate, -1200) FOR YEAR(pymt_ddate) = 2150

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jlg13]

Thanks again Mike.

Hi Scott, Sorry, I should know better and provide the error message! I was having a stressful day with deadlines... I got it to work and appreciate everyone's help.

Joe