×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 29-Jun-2016 09:49 -0500, Vernon Hamberg wrote:
Bill, you will still need to convert something - either the packed
column that holds the date (DO NOT do that - see other posts about
functions applied to columns that force full table scans) or the
expression using CURRENT DATE - you want to convert that to the
CYYMMDD format.
Since you don't have the TIMESTAMP_FORMAT function, and you DO have
the REPLACE function, you can use the following to get the CYYMMDD
form in a number of your date expression -
dec('1'||substr(replace(char(current date,iso),'-',''), 3, 6))
<<SNIP>>
Note: The above expression defaults\assumes a precision for the
DECIMAL scalar casting function instead of explicitly specifying a
matching precision to the column that will be referenced in the
predicate; in a test, a DEC(15) was the default effect -- despite the
CAST specification apparently defaults to DEC(5). However for the usage
from the OP I expect the SQL implicitly casts the expression to match
the data-type+precision+scale of the first operand of the [greater than]
predicate; the Query Engine can remap that effective literal to match
the attributes of a key for an access path defined over the [data of
that] column, when building a plan.
FWiW: The following expressions are some alternatives to the above;
while each is explicitly given a precision of seven, a DEC(15) was the
default effect in a test when precision was unspecified:
dec(insert(replace(char(current_date,iso),'-',''), 1, 2, '1'), 7)
dec( '1' concat to_char(current_timestamp,'YYMMDD') , 7 )
dec( insert(to_char(current_timestamp,'YYMMDD'), 1, 0, '1'), 7 )
Note: with IBM i 7.2, apparently current_date should be allowed as
the first argument of the TO_CHAR [aka VARCHAR_FORMAT] casting scalar,
per an implicit cast to TIMESTAMP(0) [i.e. TIMESTAMP with precision of
zero]; of course, there is no ease-of-use advantage for use of date over
timestamp if using those special registers, except to shorten the
overall expression by five characters :-)
midrange.com
