Date Time Attributes
As some data requires a time segment along with the date when performing downloads, the custom download tool now supports this functionality.
If using R12 or above, in order to use utilize the time aspect of date attributes it is required to have servlet version 1.2.87 or higher. If using 11i, the minimum shared package version will need to be 5.4.17.
If the above criteria is met, then when creating a new download query and it contains a DATE type column, you can navigate to the ‘Settings’ tab and set the ‘Allow Date Timestamp’ to Yes.
Setting Up a Query
When hard coding date values into custom queries you should always use TO_DATE to explicitly state the date format e.g. TO_DATE('1-JAN-2021', 'DD-MON-YYYY'). Custom Query currently uses a default date format of YYYYMMDD, however this could be subject to change. It can be difficult to analyze issues without explicit date formatting because these queries often run without issues when run through SQL Developer.
Without explicitly stating the format you are likely to run into “SQL Error:com.more4apps.r12.servlet.tjava.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected”.
Downloading using the Queries
If downloading using the Download form, there is the ability to filter using date ranges. To do this when using the form select the data column that was set up in your query, then for your operator choose ’Between’. This will cause the Download form to dynamically change and show a ‘Between Form’ button.
When this is selected, a new form will appear providing input for the ‘From Value’ and ‘to Value’ date fields/ranges.
When all filter criteria is entered and the download is performed, the date will be printed in the following format; keeping note that the time segment has been downloaded and can be viewed in the cell value frame.
If using the ‘Download from sheet’ then the exact date must be specified, as this method of download will assume the operator for the date as an equals (=). If a greater than filter is required we recommend using the Download form.
In the scenario where the servlet or shared package versions do not meet the minimum requirements, the download functionality will still work. However if a query has a date column in it, then the time segment will simply be excluded. If it is attempted to include the time segments when building a new download query, the following will be shown:
Click ‘OK’ and a URL link will be copied to the clipboard that can be used to download the latest servlet version for this functionality.
Refer to section: