DATE

To configure a Date field either in filter or retrieval criteria, set the field type as DATE and configure the simple mapping to map the field with the SQL parameter.

Following is an example of filter by a date field.

Include the date field in the where clause of the SQL. In the following example, published_date which is a date field from the book model is illustrated.

The filter should be defined as follows:

Following is an example of display a date field.

Include the date field in the select part of the SQL. In the following example, published_date which is a date field from the book model is illustrated.

select published_date published_date FROM ZBKS_Book

The retrieval should be defined as follows:

Following are the key points to note:

• LevelType in the FieldRef is pointed to Book, which is where the PublishedDate field is defined.

• Field Category is set to PDF(PreDefined Field)

• Type is set as DATE (Note the capital letters)

• SimpleMapping is defined in the CustomRetrievalField

• SimpleMapping contains sqlName as an attribute whose value references to the SQL parameter defined in the select clause of the SQLDef

• The default behavior of Date retrieval fields is to display using the default timezone selected in User Profile settings. However, a specific timezone can optionally be specified for a date retrieval field. To specify timezone, provide the <Timezone/> element in your CustomRetrievalField and reference a column which selects the ID for the desired timezone (for example: 'US/Pacific'). In many cases, developers get timezone by joining the SITE table and fetching SITE.TIMEZONE_ID column.

Sometimes you may wish to filter based on a "local time", for example "Jan 3rd in the Ship-From Site's TimeZone". This would return Shipments for Jan 3rd midnight Eastern time and Jan 3rd midnight Pacific time, even though the Pacific shipment might fall on Jan 2nd in the user's time zone of Eastern.

One Network offers a mapping type called DateMapping with an attribute called localFormat for these use cases. By using dateMapping with the localFormat attribute, the user's input date can be transformed into a numeric value representing year, month, day, hour, minute, second and millisecond. For example, "20140103" represents January 3rd of 2014. The developer can then adjust the report's sqlDef to use this numeric value for comparison. This is particular useful if you add triggers or workflow code to compute and format values in the relevant timezone and store them as additional columns on your transaction tables. Let's explore this feature through an example.

This feature requires two steps to get the report to work as desired:

• We need a column in the database to store numeric, "local date" value. This could be done from workflow code. In this example, PUBLISHED_DATE_NMBR column will store a "local date" version of PUBLISHED_DATE. For example if the published date is June 8th 2015 5:00:05.123 pm and the desired formatting used is yyyyMMddHHmm, PUBLISHED_DATE_NMBR will have 201506081700. If the published date is June 9th 2015 5:00:12.235 pm and the formatting used is yyyyMMddHHmmssSSS, PUBLISHED_DATE_NMBR will have 20150609170012235.

• We need a Date filter with DateMapping which includes a matching localFormat value. If PUBLISHED_DATE_NMBR is formatted using yyyyMMddHHmm, localFormat should also be yyyyMMddHHmm.

Often, a report is designed to match field-for-field with an inbound interface, that way a user can export the report as CSV, modify the CSV, then upload the result to modify the records. This requires the date fields to be formatted in a specific way to meet the requirements of the interface, and this format may not match the user's preferred date format.

To specify a format to use when exporting to CSV which should override the user's preferred format, use the <DateMapping/> element instead of the <SimpleMapping/> element, and provide the "csvFormat" attribute with your desired format (as per java.text.SimpleDateFormat). For example: