iSoftwerks.factory

The VARCHAR_FORMAT function returns a character string representation of the first argument in the format indicated by the optional format-string. Alternately, the function TO_CHAR is synonymous with VARCHAR_FORMAT.

Convert to Character Function

.
.
.
SELECT ITEMNO, TO_CHAR(LASTMNT, 'DD-Mon-YYYY') FROM ITMMST
.
.
.
D DateChar        S             10A                             
D DtTmStamp       S               z   inz(*SYS)   


EXEC SQL                                              
   set :DateChar  = TO_CHAR(:DtTmStamp, 'MM-DD-YYYY') ;
.
.
.
SELECT VARCHAR_FORMAT(RECEIVED,'YYYY-MM-DD HH24:MI:SS')
  INTO :TVAR
  FROM CORPDATA.IN_TRAY 
  WHERE SOURCE = 'CHAAS'
.
.
.

Function invocation                          Result
-------------------                          ------
VARCHAR_FORMAT(TMSTAMP,'YYYYMMDDHHMISSFF3')  20070309020738123
VARCHAR_FORMAT(TMSTAMP,'YYYYMMDDHH24MISS')   20070309140738
VARCHAR_FORMAT(TMSTAMP,'YYYYMMDDHHMI')       200703090207
VARCHAR_FORMAT(TMSTAMP,'DD/MM/YY')           09/03/07
VARCHAR_FORMAT(TMSTAMP,'MM-DD-YYYY')         03-09-2007
VARCHAR_FORMAT(TMSTAMP,'J')                  2454169
VARCHAR_FORMAT(TMSTAMP,'Q')                  1
VARCHAR_FORMAT(TMSTAMP,'W')                  2
VARCHAR_FORMAT(TMSTAMP,'IW')                 10
VARCHAR_FORMAT(TMSTAMP,'WW')                 10

The result is a representation of timestamp-expression in the format specified by the format-string. The format-string is interpreted as a series of individual elements that can be separated by one or more specific characters. A string of characters in format-string is interpreted as the length of the format element that matches an element in the timestamp-expression. If two format elements are composed of the same character and they are not separated by a separator character, the specification is interpreted, starting from the left, as the length of thet element that matches an element from the source, and continues until matches are found for the remainder of the format string. For example, DDYYYY would be interpreted as DD followed by YYYY, rather than D followed by DY, followed by YYY. For a complete list of format values, check IBM documentation.