CA IDMS JREPORT Datetime Selection
Article ID: 140429
Updated On:
Products
Issue/Introduction
It may be desirable to run a Journal Report (JReport) which only selects journal records within a specific date-time range. There are mul;tiple date fields in the journal record layout, so it is confusing as to which one to use and what format the date should be in.
This article provides an example of the syntax that should be used to pull all of the journal record types for a specific local date-time range.
Environment
Release : 19.0
Component : CA IDMS
Resolution
The first step in running a date/time based query against the journal records is to obtain a hex representation of the desired date(s). This can be done using a query like this one:
SELECT
HEX(TIMESTAMP('2019-01-27-15.00.00.0000')) FROM SYSCA.SINGLETON_NULL;
*+
*+ HEX(FUNCTION)
*+ -------------
*+ 0167E7AD2F000000
*+
*+ 1 row processed
(Note: SYSCA.SINGLETON_NULL is a dummy SQL table with one table
and one column, with the value NULL. Its purpose is to be used in situations
such as this where SQL is needed to generate certain values but no underlying
data tables need to be referenced.)
You can then use these values in the culprit input to SELECT in any JReport. In order to select all journal record types for a journal report based on a timestamp, the GMT field should be used in the comparison. Here's an example of that syntax.
SELECT
* GMT GE X'0167EF0D2F000000' AND
* GMT LE X'01680BAD2F000000'
This sample will select all journal records between 1/27/19 and 3/27/19, 3:00 pm both days.
Additional Information
Knowledge article 21479 demonstrates this same principle against the DTESTAMP journal record field:
Information and instructions for submitting journal reports can be found here:
Feedback
