Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Use variables dates in DATEDIF functions

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Use variables dates in DATEDIF functions
 Login/Join
 
Member
posted
Hi,

I try to get the number of days between 2 dates that my report ask to my users, but always I received a "0" value.

This is where I make my WHERE questions:

WHERE ( REPARTOCIRCULACION.ENTREGAFECHA GE '&ENTREGAFECHA' ) AND
( REPARTOCIRCULACION.ENTREGAFECHA LE '&ENTREGAFECHA2' );


This is my COMPUTE where I use variables dates:

COMPUTE Numdias/I3 = DATEDIF(&ENTREGAFECHA, &ENTREGAFECHA2, 'D');

Is valid to use variable dates in the DATEDIF function?

how I could I get the number of days of the lapse of time that my users select in the report?

Thanks for your help!

greetings!
Tony

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


WebFocus 7.6.10, windows 7, win server 2003-2008 PDF, TXT, XML, html, xls, doc
 
Posts: 22 | Registered: January 06, 2011Report This Post
Member
posted Hide Post
Hi
just try to add quotes to the vars
COMPUTE Numdias1/I3 = DATEDIF('&ENTREGAFECHA', '&ENTREGAFECHA2', 'D');

greetings
Andy
 
Posts: 2 | Location: Vienna | Registered: January 14, 2008Report This Post
Silver Member
posted Hide Post
Hi Tony,
Dates in WebFocus can be a little tricky. You have to use an I8 format for date function. So you will first need to convert your &variable dates and then use the datedif function.
If your users use a mm/dd/yyyy format, the following code will work.

-DEFAULT &begindate = '06/01/2009';
-DEFAULT &enddate = '12/31/2009';

-SET &beginfmt = DATECVT(EDIT(&begindate,'99$99$9999'),'A8MDYY','I8');
-SET &endfmt = DATECVT(EDIT(&enddate,'99$99$9999'),'A8MDYY','I8');
-SET &total_days = DATEDIF(&beginfmt, &endfmt,'D');

The EDIT in the date convert function simply strips the '/' characters out of the user entered date.

Happy Friday Smiler

Emily


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Member
posted Hide Post
Thanks for your answers, but How i could use via my DataFile?

(add quotes to the vars doesn´t work)

Greetings!
Tony


WebFocus 7.6.10, windows 7, win server 2003-2008 PDF, TXT, XML, html, xls, doc
 
Posts: 22 | Registered: January 06, 2011Report This Post
Virtuoso
posted Hide Post
Try this

COMPUTE Numdias/I3 = DATEDIF('&ENTREGAFECHA.EVAL', '&ENTREGAFECHA2.EVAL', 'D');


this might solve you problem

did you also add
- SET &ECHO=ALL;

at the start of your program?

and now alter the output format to an hold file and see what the echo shows you?

The suggestion of Emily could also help you to show what the user did type in for the begin en enddate amper values.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
There appears to be a small typo here:

quote:

WHERE ( REPARTOCIRCULACION.ENTREGAFECHA GE '&ENTREGAFECHA' ) AND
( REPARTOCIRCULACION..ENTREGAFECHA LE '&ENTREGAFECHA2' );

- two periods between the SEGNAME and FIELDNAME -

I assume because you are still testing that you are entering the dates in a consistent format, but as Emily points out you may have to strip out unwanted characters and re-arrange if you allow users to type in the dates. The only useful date format is YYMD (which happens to be the SI standard, so why the heck do people still write MM/DD/YY ! ... grrrrr). When you get into production I would strongly suggest you only allow your users to use the calendar picker, so the dates automatically get returned as YYMD format and the users can't 'muck' up by putting in dates wrong.

George


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Virtuoso
posted Hide Post
I tested this and it works as expected:
-SET &ENTREGAFECHA  = '2009/06/09';
-SET &ENTREGAFECHA2 = '2010/06/09';

TABLE FILE CAR
PRINT COUNTRY
COMPUTE Numdias/I8 = DATEDIF('&ENTREGAFECHA', '&ENTREGAFECHA2', 'D');
END
It does not matter if the dates are with or without slashes, it just works. Try the above code and see if it works for you also.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
What format are your dates in ?

The database one and the amper variables.

REPARTOCIRCULACION.ENTREGAFECHA
&ENTREGAFECHA
&ENTREGAFECHA2


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Member
posted Hide Post
Originally my field looks like this: DMYY (across Latin America use this format.) I Change the format as suggested by a DEFINE: entregafecha / YYMD = REPARTOCIRCULACION. ENTREGAFECHA;
Subsequently use the following question:

WHERE (REPARTOCIRCULACION.entregafecha GE '& entregafecha') AND (REPARTOCIRCULACION.entregafecha LE '& entregafecha2');
I put a calendar control for my users to choose the date

and ended up using the function:

COMPUTE Numdias/I3 = DATEDIF (& entregafecha, & entregafecha2, 'D');

When I run the report, I used the dates:

entregafecha = 2011/05/05 and entregafecha2 = 2011/05/09

and the result in Numdias is equal to "-36"

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


WebFocus 7.6.10, windows 7, win server 2003-2008 PDF, TXT, XML, html, xls, doc
 
Posts: 22 | Registered: January 06, 2011Report This Post
Virtuoso
posted Hide Post
this should work

-SET &ECHO=ALL;

-PROMPT &STARTDATE_DDMMYYYY.Give the startdate.
-PROMPT &ENDDATE_DDMMYYYY.Give the end date.
-DEFAULT &STARTDATE_DDMMYYYY=11052011;
-DEFAULT &ENDDATE_DDMMYYYY=19052011;
-SET &DAY1=EDIT(&STARTDATE_DDMMYYYY,'99$$$$$$');
-SET &MAAND1=EDIT(&STARTDATE_DDMMYYYY,'$$99$$$$');
-SET &YEAR1=EDIT(&STARTDATE_DDMMYYYY,'$$$$9999');
-SET &STARTDATE=&YEAR1||&MAAND1||&DAY1;
-TYPE &STARTDATE
-SET &DAY2=EDIT(&ENDDATE_DDMMYYYY,'99$$$$$$');
-SET &MAAND2=EDIT(&ENDDATE_DDMMYYYY,'$$99$$$$');
-SET &YEAR2=EDIT(&ENDDATE_DDMMYYYY,'$$$$9999');
-SET &ENDDATE=&YEAR2||&MAAND2||&DAY2;
-TYPE &ENDDATE

DEFINE FILE CAR
NUMDAYS/I10 WITH CAR = DATEDIF('&STARTDATE','&ENDDATE','D');
END
TABLE FILE CAR
PRINT NUMDAYS
END




It is not the most elegant way, but I used it many years ago when we did not have so much functions to translate.

It does not handle any typing errors




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
quote:
and the result in Numdias is equal to "-36"

As websmash already said in his first reply to this, you have to add quotes around the variables. If you don't you'll get -36 as a result, if you do, you get 4.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Member
posted Hide Post
Perfect!

is working properly!

thank you very much to all who answered!

Greetings from Mexico!

Tony


WebFocus 7.6.10, windows 7, win server 2003-2008 PDF, TXT, XML, html, xls, doc
 
Posts: 22 | Registered: January 06, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Use variables dates in DATEDIF functions

Copyright © 1996-2020 Information Builders