Note that the original /solution/ would be victim to the variability
of the DATFMT established for the SQL, for lack of the Date Format
having specified explicitly as the desired formatting on the second
argument. Unlikely a desirable effect. Also the lack of a space
preceding the commas in the SUBSTR scalar, the expression is not
internationalized; i.e. it will fail when the comma is the decimal
separator, per DECMPT option, which is variable like the DATFMT option.
The original expression rewritten to resolve those issues:
SUBSTR(CHAR(MIN(dayid),ISO), 6, 2) CONCAT '/' CONCAT
SUBSTR(CHAR(MIN(dayid),ISO), 9, 2) CONCAT '/' CONCAT
SUBSTR(CHAR(MIN(dayid),ISO), 3, 2) AS StartDate
The following expression is a much more succinct /solution/ for
forcing the value of the DATE data type to be cast to the
character-string with formatting of MM/DD/YY:
INSERT( CHAR( MIN(dayid), USA ), 7, 2, '')
If the "LOCAL" setting is not sufficient for its reflecting the
user's desired date presentation in casting from date to character
string using VARCHAR() or CHAR(), such that instead one would want to
force a fixed\static format for presentation, then the VARCHAR_FORMAT
scalar might be desirable for clarity, even if not as succinct as the
above use of the INSERT() and CHAR() scalars; e.g. in v6r1:
right( varchar_format( timestamp(dayid, '00.00.00') , 'YYYY/MM/DD'), 8)
http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscavarcharformat.htm?
On IBM i 7.1 the following which is more succinct due to support for
the YY formatting string:
varchar_format( timestamp(dayid, '00.00.00') , 'YY/MM/DD')
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscavarcharformat.htm
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Built-in functions -> Scalar functions
_i VARCHAR_FORMAT i_
"The VARCHAR_FORMAT function returns a character representation of a
timestamp in the format indicated by format-string.
...
Syntax alternatives: TO_CHAR is a synonym for VARCHAR_FORMAT.
..."
midrange.com
