Many of our ERP based reports are based on transaction date ranges. We are struggling with the best way to handle these and the advice we have been given doesn't seem to pass the date range to the database engine. All our dates are traditional DB date-time stamps. Our users want to enter dates (in prompts) in MDYY format. I have included code for a sample query.

Report:
TABLE FILE DLYSTKLV
PRINT
CONO
PCLS35
PNUM
DLYSTKL_Year
DLYSTKL_Month
DLYSTKL_Date
WHERE CONO EQ 'MD';
WHERE PCLS35 EQ 'LS' OR 'PA' OR 'MS';
WHERE ( DLYSTKL_Date GE &Start_Date ) AND ( DLYSTKL_Date LE &End_Date );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET WEBVIEWER ON
ON TABLE SET WEBVIEWTARG OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

Generated SQL:
SELECT
T1."DTSL",
T1."CONO",
T1."PNUM",
T2."PCLS35"
FROM
DataMart.dbo.DLYSTKLV T1,
DataMart.dbo.INP35 T2
WHERE
(T2."CONO35" = T1."CONO") AND
(T2."PNUM35" = T1."PNUM") AND
(T1."CONO" = 'MD') AND
(T2."PCLS35" IN('LS', 'PA', 'MS'));
RECORDS IN TABLE= 0 LINES= 0

Daily stock level file:
FILENAME=dlystklv, SUFFIX=SQLMSS , $
SEGMENT=DLYSTKLV, SEGTYPE=S0, $
FIELDNAME=DTSL, ALIAS=DTSL, USAGE=HYYMDs, ACTUAL=HYYMDs, FIELDTYPE=R, $
FIELDNAME=CONO, ALIAS=CONO, USAGE=A2, ACTUAL=A2, FIELDTYPE=R, $
FIELDNAME=PNUM, ALIAS=PNUM, USAGE=A15, ACTUAL=A15, FIELDTYPE=R, $
FIELDNAME=STRC, ALIAS=STRC, USAGE=A2, ACTUAL=A2, FIELDTYPE=R, $
FIELDNAME=LOCN, ALIAS=LOCN, USAGE=A15, ACTUAL=A15, FIELDTYPE=R, $
FIELDNAME=SPHY, ALIAS=SPHY, USAGE=P15.3, ACTUAL=P7, FIELDTYPE=R, $
FIELDNAME=SALC, ALIAS=SALC, USAGE=P15.3, ACTUAL=P7, FIELDTYPE=R, $
FIELDNAME=SAVL, ALIAS=SAVL, USAGE=P15.3, ACTUAL=P7, FIELDTYPE=R, $
FIELDNAME=SFRZ, ALIAS=SFRZ, USAGE=P15.3, ACTUAL=P7, FIELDTYPE=R, $
DEFINE DLYSTKL_Date/MDYY=HDATE(DTSL, 'MDYY'); $
DEFINE DLYSTKL_Year/I4=HPART(DTSL, 'YEAR', 'I4'); $
DEFINE DLYSTKL_Month/I2=HPART(DTSL, 'MONTH', 'I2'); $

Item master:
Partial view:
FILENAME=inp35, SUFFIX=SQLMSS , $
SEGMENT=INP35, SEGTYPE=S0, $
FIELDNAME=CONO35, ALIAS=CONO35, USAGE=A2, ACTUAL=A2, FIELDTYPE=R, $
FIELDNAME=PNUM35, ALIAS=PNUM35, USAGE=A15, ACTUAL=A15, FIELDTYPE=R,
TITLE='Item#', $
FIELDNAME=ACTF35, ALIAS=ACTF35, USAGE=A1, ACTUAL=A1, FIELDTYPE=R, $
FIELDNAME=PDES35, ALIAS=PDES35, USAGE=A36, ACTUAL=A36, FIELDTYPE=R,
TITLE='Description', $

Join:
JOIN
INNER DLYSTKLV.DLYSTKLV.CONO AND DLYSTKLV.DLYSTKLV.PNUM IN DLYSTKLV TO UNIQUE
INP35.INP35.CONO35 AND INP35.INP35.PNUM35 IN INP35 TAG J3 AS J3
END

We are on version 7.7.01 using SQL Server 2005

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

Since my post has several views and no response, I'll ask a more specific question:
If you define a field that is derived from an indexed date field and you use the defined date field in your report should the SQL statement then use the actual date field referenced?

Dan,

Be using dialogue manager to create timestamp field from your user input and be testing that value on real columns and not defined. I see in FP that this being not good.

I have be seeing DT(&dateinput) also but not be trying it.

Kofi

Why are you DEFINEing the Date fields, and are they DLYSTKL_Date, DLYSTKL_Date, etc?

You can filter on the Date-Time Fields with something like this:

  
WHERE AS_OF_TMS GE DT(&ENDDATE 00:00:00.000) AND AS_OF_TMS LE DT(&ENDDATE 23:59:59.999)

Dan,

Can't speak for SQL Server, but in Oracle, I get around this a couple of ways.

First, instead of using a DEFINE, I put 2 fields in the .mas, something like this.

FIELDNAME=DTSL, ALIAS=DTSL, USAGE=HYYMDs, ACTUAL=HYYMDs, FIELDTYPE=R, $
FIELDNAME=DTSLDT, ALIAS=DTSL, USAGE=MDYY, ACTUAL=DATE, FIELDTYPE=R, $

Then I will add 1 day to the &END_DATE and my where clause will look something like this:

WHERE ( DTSLDT GE '&Start_Date' ) AND ( DTSLDT LT '&End_Date' );

This will generate the proper test for a datetime stamp. Since time is not part of &Start_Date or &End_Date, Oracle will make the time midnight (00:00:00.00000).