×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

FOX 2.6 Changing the year of a date field Only
2

FOX 2.6 Changing the year of a date field Only

FOX 2.6 Changing the year of a date field Only

(OP)
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

RE: FOX 2.6 Changing the year of a date field Only

JLG13,

CODE

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.

RE: FOX 2.6 Changing the year of a date field Only

(OP)
Thanks Scott. I will give this a go.

RE: FOX 2.6 Changing the year of a date field Only

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

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

Tamar

RE: FOX 2.6 Changing the year of a date field Only

(OP)
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?

RE: FOX 2.6 Changing the year of a date field Only

(OP)
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

RE: FOX 2.6 Changing the year of a date field Only

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."

RE: FOX 2.6 Changing the year of a date field Only

Quote:

Can I execute your Replace code at the command prompt?

Yes, provided the relevant table is open and selected.

Quote:

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

CODE -->

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

RE: FOX 2.6 Changing the year of a date field Only

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close