Convert Gregorian Date Prompt Value to Julian Format

 

Can a Gregorian date prompt value be converted once to a constant that's a 6 position numeric value in Julian CYYDDD format for use in a condition?
Abstract

How to convert a Gregorian to a Julian date in prompt

Having users enter a Julian date instead of a Gregorian date is not practical except for a few power users. Users are not familiar with Julian date format and are prone to entering an incorrect value. The majority of queries include a date prompt condition. When a Gregorian date is entered in the prompt, each condition comparison must convert the Julian data element using the date wrapper so that it will match the Gregorian prompt.

This repeated conversion using the date wrapper on each condition check greatly increases CPU time. More importantly for the end user is that the actual run time increases some times to a level where running the query is not acceptable. If the Gregorian date type prompt can be converted one time to a Julian numeric 6 0 formats then the constant date conversion using the date wrapper would not be necessary. Query CPU and run time would be greatly reduced and the end user would use the more friendly Gregorian date format when prompted.

We have done a number of tests and have regularly been able to reduce CPU and actual run time to 20% by eliminating the date wrapper and using a Julian date numeric 6 0 prompt.  Many queries are not run as often as preferred causing information delays and decisions made based on old data.  iSeries Administrators constantly complain of showcase's (Query), drag on overall performance.

Solution

This is not supported. Converting an input variable to a literal value in another format will be done by the Query Optimizer each time the conditional expression is evaluated.

Ref#: 1483257