We have an audit that logs quite a bit of stuff, but we need to create something that does a clean up after 5 days. For some reason the DEFINE FILE FILELIST below isn't adding the new column. Anyone see something wrong with the following code that would prevent NEWDATE from showing up?

-SET &THENDTTM = DATEADD(&YYMD, 'D', -5);
-SET &THENDTTM = EDIT(&THENDTTM,'9999') | '/' | EDIT(&THENDTTM,'$$$$99') | '/' | EDIT(&THENDTTM,'$$$$$$99');
-TYPE THENDTTM = &THENDTTM

APP QUERY audit_logger HOLD
-RUN

TABLEF FILE FOCAPPQ
PRINT
	DATE
	TIME
	FILENAME
ON TABLE HOLD AS FILELIST
END
-RUN

DEFINE FILE FILELIST
NEWDATE/YYMD = DATE;
END
-RUN

TABLE FILE FILELIST
PRINT *
-*WHERE NEWDATE LT '&THENDTTM';
END

This message has been edited. Last edited by: GavinL,

Use ...

PRINT * NEWDATE
instead of ...

PRINT *

Thanks, I figured that out, but I come to the conclusion I must be overthinking this process, because it's kicking my @$$. I can have this written in 2 minutes within .NET, but using FOCUS, it's been 2 days.

Date conversions are an absolute nightmare in WebFocus. That really needs to be worked on.

How exactly does

DATEADD(&YMD, 'D', -10)

Result in:

160397

Looks like it convert it to an int and just subtracted 10 from it, unless there is 97 days in March all of a sudden.

Help clearly gives an example with use of string date.

Example: Using the Weekday Unit
If you use the weekday unit and a Saturday or Sunday is the input date, DATEADD changes the input date to Monday. The function

DATEADD('910623', 'WD', 1)

Looks like I can't use real dates:

-SET &NOW = HGETC(10, 'HYYMDm');
-SET &THENDTTM = DATEADD(&NOW, 'D', -&KEEPDAYS);

Throws errors:

(FOC281) ALPHA ARGUMENTS IN PLACE WHERE NUMERIC ARE CALLED FOR

Kind of throws out the help that states:

Syntax: How to Add or Subtract a Date Unit to or From a Date 
DATEADD(date, 'component', increment)
where:

date 
Date

Is a full component date.

quote:

Is a full component date.

Gavin,

This is the important piece, a full component date. Not a partial representation such as &YMD but a full date representation as in &YYMD.

Folks have always had trouble in getting date conversion / addition / movement functions to work and it is usually down to a misunderstanding of what the documentation states. Whether that calls for a legacy date, full component date etc.

Once it clicks then ......

Good luck

T

I was using &YYMD first and I get the same results. It subtracts 10 as a number, not a date. I've figured it out though.. I'll post it as Sharing.