Hello, I'm trying to get a display all items that have a date greater then the date entered. But the date is in DB2 and has a timestamp and it is having a problem. Here is what I have and it does not work. Any help will b greatly appreciated.


TABLE FILE CS_INV
PRINT
'CS_INV_ACTV.CS_INV_ACTV.INV_STAT_CD'
'CS_INV.CS_INV.INV_SER_NBR_TXT'
CTGY
SUBTYPE
INVTYPE
'CS_INV.CS_INV.INV_ACQR_DT'
'CS_INV.CS_INV.INV_ORIG_CST'
'CS_INV.CS_INV.ORIG_COST'
BY 'OD_LOC.OD_LOC.LOC_CD'
BY 'OD_LOC_TYPE_DOMN.OD_LOC_TYPE_DOMN.LOC_TYPE_CD'
BY 'OD_LOC.OD_LOC.LOC_FULL_NME_TXT'
BY LOC_TYPE
BY 'OD_LOC.OD_LOC.LOC_SEQ_NBR'
BY 'CS_INV_ACTV.CS_INV_ACTV.INV_CCN_ID'
WHERE NOT INV_TYPE_CD IN (18,53);
WHERE ((( LOC_CD EQ &LOC_CODE.(OR(FIND LOC_CD IN OD_LOC)).Please select Location Code:. )) AND ( LOC_SEQ_NBR EQ &LOC_SEQ_NBR.(OR(FIND LOC_SEQ_NBR IN OD_LOC)).Please select Seq number:. )) AND (( END_TIME GE &AS_OF_DATE ) AND ( BEG_TIME LT &AS_OF_DATE ) AND ( INV_ACQR_DT LE &AS_OF_DATE ));
WHERE ((NOT INV_STAT_CD IN (3,4,6,7)) OR (( INV_STAT_CD EQ 4 ) AND ( BEG_TIME GT DATEADD(AS_OF_DATE, 'Y', -01))))

I'm not sure what format your Date Field is but
You need to make a define and strip the timestamp out.
DDATE/YYMD = HDATE(WKEND_DATE, 'YYMD');

WEEKDT/MDYY = (DDATE);

Also please read this https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/8941065232

A timestamp is a seven-part value (year, month, day, hour, minute, second,
and microsecond) that represents a date and time as defined previously,
except that the time includes a fractional specification of microseconds.

The internal representation of a timestamp is a string of 10 bytes, each
of which consists of two packed decimal digits. The first 4 bytes
represent the date, the next 3 bytes the time, and the last 3 bytes the
microseconds.

The length of a TIMESTAMP column as described in the catalog is the
internal length which is 10 bytes. The length of a TIMESTAMP column as
described in the SQLDA is the external length which is 26 bytes. DCLGEN
therefore defines 26-byte, fixed-length string variables for TIMESTAMP
columns.


So what does your MFD show as the definition? It appears you want a range of dates regardless of the timestamp, so you can play from there. Of course it is never a good idea to select on a define field if you can avoid it.

What I'm trying to do is pull all info from a field that is great then the date entered in the AS_OF_DATE. The field being the BEG_TMST which is in timestamp verses the AS_OF_DATE is in YYMD. Everything works except this where clause, of course. I have also converted BEG_TMST to BEG_TIME = YYMD but still nothing.

WHERE ((NOT INV_STAT_CD IN (3,4,6,7)) OR (( INV_STAT_CD EQ 4 ) AND ( BEG_TIME GT DATEADD(AS_OF_DATE, 'Y', -01))))


I have been working on this for days and getting to the point of a migrain.

You seem to be testing against an & var (&AS_OF_DATE) which is a string. First you need to convert it to a date then do your DATE ADD...as in...
DATEADD((DATECVT(&AS_OF_DATE, I8YYMD, YYMD)), M, -1)

Hope this helps,

Kevin

Have you tried just screening on each of those elements of your WHERE...could be the logic there causing the problem.

I want to Thank All who replied!! It was great help.

Ana