How to select journal report records based on timestamps.

Document ID : KB000051065
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The field DTESTAMP can be used in requesting a JREPORT to specify selection criteria. This field is a hex value that is an SQL timestamp, so a desired selection criteria must first be converted to this format before it can be used in the report request.

Solution:

It is often desirable to limit the output of a Journal Report (JRreport) to records only within a specific date range. This can be done by specifying selection criteria on the DTESTAMP field when the JReport is requested, but in order to specify the date value correctly, some conversion work must be done first. The DTESTAMP element in a journal record is a hex value that is in ANSI standard SQL timestamp format. If your site has SQL, you can determine the timestamp value you need by converting the desired Julian date to a hex value in OCF via a query such as this:

SELECT HEX(TIMESTAMP('2007-05-21-01.00.00.0000')) FROM SYSTEM.TABLE;

This will return the 8 byte hex value( 0165D200E1000000) which can then be specified in a selection criteria against the DTESTAMP field to represent May 21, 2007.

If your site doesn't have the SQL option, you can do the date conversion via a call to IDMSIN01, which can convert a 26-byte displayable date/time to an 8-byte hex value. Examples of how to do this are in the IDMS Callable Services Manual, chapter 2.0.

Once you've got the date/times you want to use in selecting records, you can run the JReport request with a select statement that looks like this:
SELECT TYPE EQ ('BFOR' 'AFTR' 'COMT' 'ENDJ' 'RTSV') AND
* DTESTAMP GE X'0164718FF3C00000' AND
* DTESTAMP LE X'0164718FFB400000'

This will limit the output of the JReport to those journal records which are within the dates specified.