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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Date comparison
 Login/Join
 
silver179
Member
posted
I am currently trying to compare two user entered dates to a date pulled from the database (HYYMDs format.) Direct comparison gives me an alpha comparison error and the method I'm currently trying snags on the server.


WHERE ( ENTRANCE_DATETIME GE ( HINPUT(14, &txtBDate, 8, 'HYYMDS') ) );
WHERE ( ENTRANCE_DATETIME LE ( HINPUT(14, &txtEDate, 8, 'HYYMDS') ) );
I've tried a few other ways, but all with one of the results above. Any ideas, suggestions, bonks on the head would be much appreciated.

Thanks!
-Stephanie

This message has been edited. Last edited by: <Maryellen>,


- Stephanie Pierce
WF 7.1.4
Win2K
 
Posts: 15 | Location: Texas | Registered: July 19, 2005Report This Post
codermonkey
Platinum Member
posted Hide Post
Quick clarification. Your prompt for &TxtBDate -- are you getting just the date component from the user or date and time?
 
Posts: 118 | Location: DC | Registered: May 13, 2005Report This Post
silver179
Member
posted Hide Post
Just the date from the user.
 
Posts: 15 | Location: Texas | Registered: July 19, 2005Report This Post
codermonkey
Platinum Member
posted Hide Post
I've not work as much with date-time format, but I believe HINPUT is expect a string of 14 char as first input and you are supplying an 8 character string -- 20050830 (or something like that).
 
Posts: 118 | Location: DC | Registered: May 13, 2005Report This Post
codermonkey
Platinum Member
posted Hide Post
I'm also never seen any date function used directly in a WHERE statement -- typically they are coded in an amper variable or define field --my guess is because it can't be parsed correctly.
 
Posts: 118 | Location: DC | Registered: May 13, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
While I haven't used the function directly in a WHERE statement, I think it should work.

Have you tried putting the amper variable in quotes:

WHERE ( ENTRANCE_DATETIME GE ( HINPUT(14, '&txtBDate', 8, 'HYYMDS') ) );

or

WHERE ( ENTRANCE_DATETIME GE ( HINPUT(14, '&txtBDate.EVAL', 8, 'HYYMDS') ) );

As Pam mentioned, you hve to provide a 14 character date-time to HINPUT, something like '20050830010000'. You could code it something like this, (I haven't tested it):

WHERE ( ENTRANCE_DATETIME GE ( HINPUT(14, '&txtBDate.EVAL.000000', 8, 'HYYMDS') ) );
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
This works:

-SET &DATE1 = '19990301';

TABLE FILE BASEL_TIME_D
PRINT
PERIOD_START_DT
PERIOD_END_DT

WHERE ( PERIOD_START_DT GE ( HINPUT(14, '&DATE1..000000', 8, 'HYYMDS') ) );
WHERE ( PERIOD_START_DT LE ( HINPUT(14, '&DATE1..000000', 8, 'HYYMDS') ) );
END

(No EVAL necessary).
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
You don't need the double periods in &DATE1..000000, you could code it as &DATE1.000000.

You used to need the double periods, non?
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Spence
Platinum Member
posted Hide Post
the todate and fromdate are entered by picking a month, day, and year.
-SET &TODATE1 = &TYR||&TMO||&TDA;
-SET &TODATE2 = AYMD(&TODATE1, 1, 'YYMD');
-SET &TMM = EDIT(&TODATE2,'$$$$99$$');
-SET &TDD = EDIT(&TODATE2,'$$$$$$99');
-SET &TYY = EDIT(&TODATE2,'9999$$$$');
-SET &TODATE = &TYY|| '/' ||&TMM|| '/' ||&TDD;
-*************************************************************
-SET &FROMDATE = &YR|| '/' ||&MO|| '/' ||&DA;
-*************************************************************
DEFINE FILE XXX
ACTUAL_DECISION_DATE/YYMD = HDATE(ACTUAL_DECISION_DATE, 'YYMD');
END
TABLE FILE XXX
PRINT XXX
WHERE ACTUAL_DECISION_DATE GE '&FROMDATE'
AND ACTUAL_DECISION_DATE LT '&TODATE';
END
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Report This Post
jimster06
Guru
posted Hide Post
Suggestion regarding Francis' neat solution:

Use &DATE1.000000 for the lower-bound
and &DATE2.235959 for the upper-bound

if that makes sense for the particular solution.
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
<toby mills>
posted
I don't know what your date looks like on the way in... I'm presuming this is a timestamp format on your sql database right?

If your incoming date look something like 08-10-2005 (or with slashes), I've been having good luck with:


WHERE ACTUAL_DECISION_DATE GE DT(&FROMDATE). I haven't really seen that doc'd anyplace besides running into it on techsupport. This works for me against sql server 2000.

Good luck!

-Toby
 
Report This Post
silver179
Member
posted Hide Post
Thank you! Took some tweaking of how the date was entered by the user, but the code worked.


-SET &DATE1=&txtBDate;
-SET &DATE2=&txtEDate;

WHERE ( ENTRANCE_DATETIME GE ( HINPUT(14, '&DATE1.000000', 8, 'HYYMDS') ) );
WHERE ( ENTRANCE_DATETIME LE ( HINPUT(14, '&DATE2.235959', 8, 'HYYMDS') ) );

This message has been edited. Last edited by: <Maryellen>,


- Stephanie Pierce
WF 7.1.4
Win2K
 
Posts: 15 | Location: Texas | Registered: July 19, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders