How to return records for the current month

January 10, 2008, 11:07 AM

<BrianS>

I'm converting an Access query to WebFocus. I'm wanting the query to run every month and return the records for that month without having to manually change the dates in the Where statement. In Access we use the following statemnt

DatePart("m",Now())

Is there anything that will do the same thing for Webfocus? The only thing I've found is Today which returns data for the current day. Thanks for your help.

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

January 10, 2008, 11:28 AM

FrankDutch

Brian

Ask your management if it is allowed to spend 25 $ for a book.
If they do, order the book at IBI. It's is called
"(Almost) 1001 Ways to Work With DATES in WEBFOCUS" isbn 978-0-9791722-1-2.

You can define an actual month based upon the actual date.

actual date is &YYMD

-SET &ACTMONTH=EDIT(&YYMD,'999999');
-TYPE &ACTMONTH

But there is more.....

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7

January 10, 2008, 11:47 AM

Glenda

I must agree with Frank, that book is pure gold. You really should get a copy.

As you can see from the responses, there are several ways to resolve your problem. Here's a quick example of how I would do it.

 
-*********************************************************************
-*  THIS QUERY WILL PULL ALL EMPLOYEES WITH AND INCREASE DATE IN 
-*  JANUARY 1982
-*********************************************************************
-* &YYDM IS THE CURRENT DATE.  FOR THIS EXERCISE THE CURRENT DATE HAS 
-* BEEN MANUALLY SET TO JANUARY 10, 1982.  TO CALCULATE THE CURRENT 
-* MONTH SIMPLY USE '-SET &MONTH = &YYMD/100;
-SET &DATE     = 19820110;
-SET &MONTH    = &DATE/100;
-*********************************************************************

DEFINE FILE EMPLOYEE
-**********************************************************************
-* SINCE DAT_INC IS AN ALPHA FIELD YOU MUST CONVERT IT TO I6 IN ORDER
-* TO COMPARE IT TO &MONTH
-**********************************************************************
INCDATEA/A6 = '19'||EDIT(DAT_INC, '9999');
INCDATE/I6 = EDIT(INCDATEA);
END

TABLE FILE EMPLOYEE
PRINT
	EMP_ID
	LAST_NAME
	FIRST_NAME
	HIRE_DATE
	DEPARTMENT
	CURR_SAL
	CURR_JOBCODE
	DAT_INC 
	INCDATE
WHERE INCDATE EQ &MONTH
END

Glenda

In FOCUS Since 1990
Production 8.2 Windows