Re: An easy (I think) SQL question

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 :-)