Focal Point
add date function?
December 12, 2007, 02:08 PM
CECILIAadd date function?
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
WebFocus server 768
Developer studio 768
Windows XP
Output: Excel, HTML, PDF, AHTML
December 12, 2007, 02:23 PM
John_EdwardsI'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
December 12, 2007, 02:34 PM
GinnyJakesIf 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.
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
December 12, 2007, 02:48 PM
CECILIAThank 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...
WebFocus server 768
Developer studio 768
Windows XP
Output: Excel, HTML, PDF, AHTML
December 12, 2007, 03:39 PM
Francis MarianiFirst 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);
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 12, 2007, 03:46 PM
CECILIAThank you all!
It worked.
WebFocus server 768
Developer studio 768
Windows XP
Output: Excel, HTML, PDF, AHTML