Variable for 3 months before todays date

[testing12345]

When I start my program I would like it to clean out old records from usertable.dbf I am having problems coming up with code that deletes old records out. I have a curdate field that contains the date in the format 050412 - yymmdd. I need it to delete any record out that is more then 2 months old. What is the easiest way of doing this? Where should I begin?

Thanks,

Ross

 

[Mike Gagnon]

? GOMONTH(DATE(),-2)

Mike Gagnon

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

 

[Mike Lewis]

Ross,

Is curdate held as a date field or a character string?

If it is a date, do this:

SELECT MyTable
DELETE FOR CurDate <= GOMONTH(DATE()-2)

If it is a character string:

SELECT MyTable
DELETE FOR ;
  DATE(VAL(SUBSTR(CurDate,1,2))+2000, ;
  VAL(SUBSTR(CurDate,3,2)), ;
  VAL(SUBSTR(CurDate, 5,2))) <= GOMONTH(DATE()-2)

I haven't tested this, but it should give you the general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[testing12345]

That's awesome thank you so much for your help. Also I am trying to get that to the format yymmdd. You wouldn't happen to have a quick answer to this would you?

Thanks,

Ross

 

[jimstarr]

Hi Ross,

SET DATE TO YMD
thedate = GOMONTH(DATE(), -2) && 2 months ago
thedate = DTOC(thedate) && convert to character
thedate = STRTRAN(thedate, '/', '') && remove slashes

IF mytable.mydate < thedate
...Delete record
ENDIF


This solution assumes no dates earlier than 2000.

Jim

 

[testing12345]

Also, its a character string.

curdate = "050419"

Thanks,

Ross

 

[craigber]

To get today's date into yymmdd format:

? SUBSTR(DTOS(DATE()), 3)

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[testing12345]

I got it....Thank you so much everyone!