Hi,

Anyone know a good function can be used to increment an input date parameter (alpha format, though) by 7 days and use it in where clause? I used the following but get an error message.

WHERE REQUEST_TIME GE DT(&REQUEST_TIME) AND REQUEST_TIME LE DATEADD(DT(&REQUEST_TIME), 'D', 7);

Thanks.

Cecilia

I've only been able to get things like that to work correctly via DEFINE fields. An example:

DEFINE FILE XYZ
TODAYS_DATE_NORMAL/YYMD = HDATE(TODAYS_DATE, 'YYMD');
SIX_MONTHS_AGO/YYMD = TODAYS_DATE_NORMAL - 182;
END
TABLE FILE XYZ
.
.
.
WHERE EMPLOYMENT_STATUS EQ 'Inactive' AND EFFECTIVE_END_ID GT SIX_MONTHS_AGO
END

If you are doing the date compare against a relational data source, not all of them will pass a date WHERE screen when the table date is being compared to a DEFINE field. Teradata is one example of an RDBMS where you must specify a data literal as the right-hand side of the compare or the WHERE clause does not get passed to Teradata.

Here is a way, Cecilia, to do this with Dialogue Manager

-* Assume &REQUEST_TIME is the date coming from the launch page.
-SET &ENDDT=AYMD(&REQUEST_TIME,7,'I8YYMD');
TABLE FILE ...
PRINT ...
WHERE REQUEST_TIME GE &REQUEST_TIME AND REQUEST_TIME LE &ENDDT;

Now you didn't mention the format of the table column REQUEST_TIME. That might require an alteration of my code above. The fact that you have the word time in the name makes me think it might be a date-time stamp. Let us know if that is the case.

Thank you.

The REQUEST_TIME is a date-time stamp. However, the parameter &REQUEST_TIME is an alpha format. I got an error message when I run your code: (FOC36355) INVALID TYPE OF ARGUMENT #1 FOR USER FUNCTION AYMD

I used DT(&REQUEST_TIME) and still got the same error message...

First use Dialogue Manager to add 7 days to your date and then use the new variable in your date selection:

-SET &REQUEST_TIME1 =
- DATECVT( DATEADD(DATECVT(&REQUEST_TIME,'I8YYMD','YYMD'),'D', 7),'YYMD','I8YYMD');

The code above looks crazy, but what's happening is you have to convert the alpha string to a Date format using DATECVT, then use DATEADD to add 7 days, then use DATECVT to convert from Date format back to alpha string - read the line of code form inside going outwards.

This of course, assuming that &REQUEST_TIME is an alpha string that contains 8 characters like '20071231'.

Then your WHERE statement should be:

WHERE REQUEST_TIME FROM DT(&REQUEST_TIME 00:00:00.000) TO DT(&REQUEST_TIME1 23:59:59.999); 

Thank you all!

It worked.