Select field as date

You can interact with system platform table fields defined as date by selecting a field as a date.

The following example code uses IBS_SPEC.UPDATED_DATE as the date field to determine whether a row is needed. triActiveStartDA and triActiveEndDA are the date range. These dates come from the triActiveStartDA and triActiveEndDA fields on the transform business object.

The IBS_SPEC table is not a TRIRIGA® object. It is a system platform table that is used to track objects in TRIRIGA. It includes a field that changes every time an object in TRIRIGA is updated. The field is the UPDATED_DATE field and in the database it is a date field, not a number field.

In the following example code, ${triActiveStartDA_MinDATE} and ${triActiveEndDA_MaxDATE} are used. These wrapped dates fields fetch all records from 12:00 am on the start date to 11:59 pm on the end date.

SELECT org.SPEC_ID ORG_SPEC_ID, org.TRIORGANIZATIONLOOKUOBJID, 
space.CLASSIFIEDBYSPACESYSKEY, org.TRIALLOCPERCENTNU, org.TRIALLOCAREANU, 
space.TRIHEADCOUNTNU, space.TRIHEADCOUNTOTHERNU, spec.UPDATED_DATE
FROM T_TRIORGANIZATIONALLOCATION org, T_TRISPACE space, IBS_SPEC spec
WHERE org.TRILOCATIONLOOKUPTXOBJID = space.SPEC_ID
and space.SPEC_ID = spec.SPEC_ID
and spec.UPDATED_DATE >= ${triActiveStartDA_MinDATE}
and spec.UPDATED_DATE <= ${triActiveEndDA_MaxDATE} order by UPDATED_DATE

In Oracle or DB2, ${triActiveStartDA_MinDATE} displays like to_date (‘20070701 00:00:00’, ‘YYYYmmdd hh24:mi:ss’) and ${triActiveEndDA_MaxDATE} displays like to_date (‘20070731 23:59:59’, ‘YYYYmmdd hh24:mi:ss’).

In SQL Server, these dates look slightly different because of database specifics, but are set up to capture all the rows between the two dates.