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>,

Quick clarification. Your prompt for &TxtBDate -- are you getting just the date component from the user or date and time?

Just the date from the user.

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).

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.

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') ) );

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).

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?

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

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.

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

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>,