Re: TO_CHAR function

On 26-Mar-2015 14:07 -0500, Gary Thompson wrote:

<<SNIP>> Chuck's example is the first I've seen where format
"elements" or "tokens" are combined to get a result I had assumed
would require concat or other "assembly". <<SNIP>>

FWiW: There had always been one example in the docs, /similar/ to what I gave, though the example in the docs since v5r4 fails to show the expected output for a given input :-( The VARCHAR_FORMAT scalar in v5r4 was limited to only two exacting specific strings, making the feature mostly worthless best I can recall, as both could easily be obtained in other ways.

After I chastised IBM via the Reader Comments on v6r1, some more format-string examples were added under "Examples of valid format strings are:". Yet the one final example remains documented identically from v5r4, thus still failing to show what is output [the character host variable TVAR] for the specific format-string shown in that example [minimally because the value of the column RECEIVED is not defined]:

<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzscavarcharformat.htm>
"...
_Note_

Syntax alternatives: TO_CHAR is a synonym for VARCHAR_FORMAT.

Example

• Set the character variable TVAR to the timestamp value of RECEIVED from CORPDATA.IN_TRAY, using the character string format supported by the function to specify the format of the value for TVAR.

SELECT VARCHAR_FORMAT(RECEIVED,'YYYY-MM-DD HH24:MI:SS')
INTO :TVAR
FROM CORPDATA.IN_TRAY
..."

The option exists for anyone to further prod IBM to update the docs with worthwhile example(s); I've not checked v7r2 to see if that KnowledgeCenter doc has anything added.