I created an MFD that has a field that is a timestamp in DB2, but is set to HYYMDm format in the MFD. When I try to create a report in painter and use the timestamp field in my where clause it says that the value is invalid length. It's like the MFD format and the DB2 format don't match? I was thinking about changing the MFD's field format to A26, does anyone know if this will work?

sbp ;
Changing the Timestamp field format to A26 in the MFD will work just fine. We do it all the time.

There is only one catch though (as far as I have seen so far), you may have issues if you use a lot of SQL-passthru in your code. TS fields put to a hold file via SQL-passthru retain the HYYMDm format. If you ever have to do a FOCUS join to a TS field defined as A26, you need to make sure to CHAR() the TS from the SQL passthru.

Here is a previous thread on this topic -
https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/2881012902

Hope that helps.
Sandeep Mamidenna

TABLE FILE BASEL_TIME_MONTH_CNTL
PRINT
UPDATE_TIMESTAMP
WHERE UPDATE_TIMESTAMP EQ DT(20070208 12:47:15.729309) 
END

UPDATE_TIMESTAMP is defined in the Master as this:

FIELD=UPDATE_TIMESTAMP, UPDATE_TIMESTAMP, HYYMDm, HYYMDm, MISSING=ON, $

You must use the DT function in the WHERE statement.

The way our tables format timestamp fields is '2007-01-01-00.00.00.000000'. Will the DT() in the where clause accept the value like that?

sbp, the example I provided is a working example.

The value must be coded exactly like I show - no dashes and no apostrophes, it's treated as a numeric field.

The report ran good using the DT function. Thanks for the help wit that Francis. However it displays the date like you mentioned in your example. However I would like it to display like it is on our tables. If this is already set as HYYMDm then how would I convert the datetime format to '2007-01-01-00.00.00.000000'?

TABLE FILE BASEL_TIME_MONTH_CNTL
PRINT
UPDATE_TIMESTAMP
COMPUTE UPDATE_TIMESTAMPA/A26 = HCNVRT (UPDATE_TIMESTAMP,'(HYYMDm)', 26, 'A26');
COMPUTE UPDATE_TIMESTAMPB/A26 = EDIT(UPDATE_TIMESTAMPA,'9999$-99$-999999999999999999');
WHERE UPDATE_TIMESTAMP EQ DT(20070111 08:51:36.921850)
END

Unfortunately, as far as I know, you have to use HCNVRT and then EDIT to replace the / by -.

Don't filter on the COMPUTE or DEFINE fields - that does not get passed to SQL.

Thanks, Francis! That worked perfectly!