CA Service Management

I am trying to query assets based on creation date/last update date and no success.

last update date is stored as integer as per ITAM Schema

where Jasper functions DateRange or TimestampRange can't be converted to Integer

-----------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

Here is the SQL Query and Error while trying to create a report based on Last Modified date.

Does anyone tried creating report for Assets based on Date fields as Input parameters?

Thanks,

Samba.

Hi Samba,

Please be aware that the last_update_date, creation_date, etc. are stored in epoch date format  (these are integer datatype), and not stored as date or datetime. 

Convert your compare dates to epoch format and use them in a GT/GE and LT/LE comparison. 

Best Regards,

Gale Bacon

CA Technologies

Hi Gale,

I already converted the EPOCH format to Date or Date/Time, still it is same problem.

Thanks,

Samba.

Hi Samba,  

There is no need to convert epoch to date or date/time.  Use the epoch integer field for your comparison. 

Hi Gale,

How do you do comparison without conversion? atleast Jasper should convert Date/Time (selected in frontend) to Epoch format. It needs conversion one or other way.

Thanks,

Samba.

This is achieved using the Between parameter in SQL and Jasper parameters (in table and Main report) as SQL.Date/SQL.Time. Now we can pass the Date/time selected in frontend to SQL db.

Hi Samba,

I did a quick search for 'JasperSoft epoch date comparison' and received a few hits. 

This one looks promising: 

Greater than or equal to operators for date comparison in Jaspersoft Studio | Jaspersoft Community