Trying to create a date range in WebFocus using parameters. Problem is the "to" side of the date ends with a time of 00:00:00. So basically I am not getting the final day. Looking for some ideas.

I modified the master table to have this
FIELDNAME=MOD_PAID_DATE, ALIAS=PAID_DATE, USAGE=MDYY, ACTUAL=DATE, MISSING=ON, $
FIELDNAME=PAID_DATE, ALIAS=PAID_DATE, USAGE=HYYMDs, ACTUAL=HYYMDs, FIELDTYPE=R,
MISSING=ON, $

Then I created the report with the following where clause.

WHERE ( MOD_PAID_DATE GE '&FromModDate.From Date.' ) AND ( MOD_PAID_DATE LE ('&To_PAID_DATE.To Date.') );

Can you please provide example values for the two date variables?

Will the "to" side always end with a time of 00:00:00?

You may be able to add 1 day to the End Date-Time and use LT instead of LE, but please post example values...

Here is a sample of what the range ends up to be. I need to add a day to the "to-date or get the datetime to be at "9's"

Forgot the sample.
SELECT T1."RPT_KEY",T1."EMP_KEY",T1."PAID_DATE" FROM08.36.31 AE ConcurT.dbo.CT_REPORT T1 WHERE (T1."PAID_DATE" BETWEEN08.36.31 AE '20071001 00:00:00.000' AND '20071220 00:00:00.000');

I would not modify the Master, I would leave the date fields as HYYMDs. I would add 1 to the To Date and code the WHERE statement like this:

I guess &FromModDate.From Date. and &To_PAID_DATE.To Date. are prompts and I assume that the user enters the dates in YYMD format.

-SET &TO_DTTM1 = &TO_DATE || '000000';
-SET &TO_DTTM2 = HCNVRT(HADD(HINPUT(14, '&TO_DTTM1.EVAL' , 14, 'HYYMDS'), 'DAY', 1, 8, 'HYYMDS'), '(HYYMDS)', 20, 'A20');

...

WHERE ( MOD_PAID_DATE GE DT(&FROM_DATE) AND MOD_PAID_DATE LT DT(&TO_DTTM2)

Multiple Date-Time functions are used in the -SET statement, HINPUT to take the date-time string and turn it into a Date-Time value, HADD to add 1 DAY and HCNVRT to convwert the result from a Date-Time value back to a string.

I keep getting this error.
0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC177) INVALID DATE CONSTANT: )
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND


Here is the code

SET TRACEOFF=ALL
SET TRACEUSER=CLIENT
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT

-SET &FROM_DTTM1 = &FROM_DATE ;
-SET &TO_DTTM1 = &TO_DATE ;
-SET &TO_DTTM2 = HCNVRT(HADD(HINPUT(14, '&TO_DTTM1.EVAL' , 14, 'HYYMDS'), 'DAY', 1, 8, 'HYYMDS'), 'HYYMDS', 20, 'A20');
JOIN CLEAR *
TABLE FILE CONCUR_CT_REPORT
PRINT
RPT_KEY
EMP_KEY
BY HIGHEST PAID_DATE
WHERE ( PAID_DATE GE DT(&FROM_DTTM1) AND PAID_DATE LT DT(&TO_DTTM2));
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
END

It appears that it does not recognize DTTM2. Not sure why as I have tried several things to narrow it down.
Any ideas?

Here's a working example:

-SET &ECHO=ALL;

-SET &CUR_DTTM = &YYMD | '000000';

-SET &NEW_DTTM = HCNVRT(HADD(HINPUT(14, '&CUR_DTTM.EVAL' , 14, 'HYYMDS'), 'DAY', 1, 8, 'HYYMDS'), '(HYYMDS)', 20, 'A20');

DEFINE FILE CAR
TEST_DTTM/HYYMDs = IF COUNTRY EQ 'ENGLAND' THEN DT(20080704000000) ELSE DT(20080708000000);
END

TABLE FILE CAR
PRINT COUNTRY
TEST_DTTM
WHERE TEST_DTTM EQ DT(&NEW_DTTM);
END

The WHERE statement looks like this:

WHERE TEST_DTTM EQ DT(2008/07/08 00:00:00 );

It appears that the date-time format should be HYYMDS instead of HYYMDs in the Dialogue Manager -SET command.