The VARCHAR_FORMAT function returns a character representation of an input expression.

The schema is SYSIBM.

If any argument of the VARCHAR_FORMAT function can be null, the result can be null; if any argument is null, the result is the null value.

Character string to VARCHAR

character-expression

An expression that returns a value that must be a built-in CHAR or VARCHAR data type.
In a Unicode database, if a supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function.

The result is a VARCHAR with a length attribute that matches the length attribute of the argument.
The value of the result is the same as the value of character-expression.

The code page of the result is the code page of the section.

DATE or TIMESTAMP to VARCHAR

date-or-timestamp-expression

An expression that returns a value that must be a DATE or TIMESTAMP, or a valid string representation of a date or timestamp that is not a CLOB or DBCLOB.
In a Unicode database, if the expression returns a graphic string representation of a date or timestamp, the returned value is first converted to a character string before the function is evaluated.

If the input expression returns:

• A string, the format-string argument must also be specified.
• A DATE or a string representation of a date, the returned value is first converted to a TIMESTAMP(0) value with a time component of exactly midnight (00.00.00).
• A string representation of a timestamp, it is first converted to a TIMESTAMP(12) value

For a list of valid formats for string representations of datetime values, see String representations of datetime values.

format-string1

An expression that returns a value with one of the following built-in data types:

• CHAR or VARCHAR
• Signed numeric
• Datetime
• GRAPHIC or VARGRAPHIC (Unicode databases only)

If the data type is not CHAR or VARCHAR, it is implicitly cast to VARCHAR before the function is evaluated.
The actual length must not be greater than 255 bytes (SQLSTATE 22007).
The value is a template for how timestamp-expression is to be formatted.

The default format string is based on the value of the special register CURRENT LOCALE LC_TIME.

A valid format string must contain a combination of the format elements listed in Table 1 (SQLSTATE 22007).
Two format elements can optionally be separated by one or more of the following separator characters:

• minus sign (-)
• period (.)
• slash (/)
• comma (,)
• apostrophe (')
• semi-colon (;)
• colon (:)
• blank ( )

Separator characters can also be specified at the start or end of format-string.

Table 1. Format elements for DATE or TIMESTAMP to VARCHAR

Format element	Description
AM or PM	Meridian indicator (morning or evening) without periods. This format element is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME.
A.M. or P.M.	Meridian indicator (morning or evening) with periods. This format element uses the exact strings 'A.M.' or 'P.M.' and is independent of the locale name in effect.
CC	Century (01-99). If the last two digits of the four-digit year are zero, the result is the first two digits of the year; otherwise, the result is the first two digits of the year plus one.
DAY, Day, or day	Name of the day in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME.
DY, Dy, or dy	Abbreviated name of the day in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME.
D	Day of the week (1-7). The first day of the week is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME.
DD	Day of month (01-31).
DDD	Day of year (001-366).
FF or FFn	Fractional seconds (0-999999999999), where n specifies the scale of the returned value. Valid values for n are 1 - 12 with no leading zeros. Specifying FF is equivalent to specifying FF6. If the scale of the input timestamp is less than n, the result is padded with trailing zeros.
HH	HH behaves the same as HH12.
HH12	Hour of the day (01-12) in 12-hour format.
HH24	Hour of the day (00-24) in 24-hour format.
I	ISO year (0-9). The last digit of the year based on the ISO week that is returned.
ID	ISO day of the week (1-7). 1 is Monday and 7 is Sunday.
IW	ISO week of the year (01-53). The week starts on Monday and includes seven days. Week 1 is the first week of the year to contain a Thursday, which is equivalent to the first week of the year to contain January 4.
IY	ISO year (00-99). The last two digits of the year based on the ISO week that is returned.
IYY	ISO year (000-999). The last three digits of the year based on the ISO week that is returned.
IYYY	ISO year (0000-9999). The 4-digit year based on the ISO week that is returned.
J	Julian day (number of days since January 1, 4713 BC).
MI	Minute (00-59).
MM	Month (01-12).
MONTH, Month, or month	Name of the month in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME.
MON, Mon, or mon	Abbreviated name of the month in uppercase, titlecase, or lowercase format. The language used is dependent on locale-name, if specified; otherwise, it is dependent on the value of the special register CURRENT LOCALE LC_TIME.
MS	Milleseconds (000-999). Same as FF3.
NNNNNN	Microseconds (000000-999999). Same as FF6.
Q	Quarter (1-4), where the months January through March return 1.
RR	RR behaves the same as YY.
RRRR	RRRR behaves the same as YYYY.
SS	Seconds (00-59).
SSSSS	Seconds since previous midnight (00000-86400).
US	Microseconds (000000-999999). Same as FF6.
W	Week of the month (1-5), where week 1 starts on the first day of the month and ends on the seventh day.
WW	Week of the year (01-53), where week 1 starts on January 1 and ends on January 7.
Y	Last digit of the year (0-9).
YY	Last two digits of the year (00-99).
YYY	Last three digits of the year (000-999).
YYYY	4-digit year (0000-9999).

The format elements in Table 1 are not case sensitive, with the following exceptions:

• AM, PM
• A.M., P.M.
• DAY, Day, day
• DY, Dy, dy
• D
• MONTH, Month, month
• MON, Mon, mon

In cases where format elements are ambiguous, the case insensitive format elements will be considered first.
For example, 'DDYYYY' would be interpreted as "DD followed by YYYY", not "D followed by DY followed by YYY".

locale-name

A character constant that specifies the locale used for the following format elements:

• AM, PM
• DAY, Day, day
• DY, Dy, dy
• D
• MONTH, Month, month
• MON, Mon, mon

The specified locale name is not case sensitive and must be a valid locale (SQLSTATE 42815).
For information about valid locales and their naming, see Locale names for SQL and XQuery.
The default is the value of the CURRENT LOCALE LC_TIME special register.

The result is a representation of the input timestamp expression in the format specified by the format string.
The format string is interpreted as a series of format elements that can be separated by one or more separator characters.
A string of characters in the format string is interpreted as the longest matching format element in Table 1.
If two format elements that contain the same characters are not delimited by a separator character, the specification is interpreted, starting from the left, as the longest matching format element in the table, and continues until matches are found for the remainder of the format string.
For example, 'YYYYYYDD' is interpreted as the format elements 'YYYY', 'YY', and 'DD'.

The result is a varying-length character string. The length attribute is 255.
If the string units of the environment or format-string is CODEUNITS32, the string units of the result is CODEUNITS32.
Otherwise, the string units of the result is OCTETS.
The format-string determines the actual length of the result.
If the resulting string exceeds the length attribute of the result, the result is truncated.

The code page of the result is the code page of the section.

Signed numeric to VARCHAR

numeric-expression

An expression that returns a value of any built-in signed numeric data type.
If the data type of the value is not DECFLOAT, it is converted to DECFLOAT(34) for processing.

format-string2

An expression that returns a value that has one of the following built-in data types:

• CHAR or VARCHAR
• Signed numeric
• Datetime
• GRAPHIC or VARGRAPHIC (Unicode databases only)

If the data type is not CHAR or VARCHAR, it is implicitly cast to VARCHAR before the function is evaluated.
The actual length cannot be greater than 255 bytes (SQLSTATE 22018).
The value is used as a template to format the input decimal floating-point expression.
Format elements specified as a prefix can be used only at the beginning of the template.
Format elements specified as a suffix can be used only at the end of the template.
The template cannot contain more than one of the MI, S, or PR format elements (SQLSTATE 22018).

If a format string is not specified, the input decimal floating-point expression is formatted in the form of an SQL decimal floating-point constant.
If the input decimal floating-point expression is negative, the first character of the result is a minus sign; otherwise, the first character is a digit.
If the input decimal floating-point expression is zero, the result is 0.

Table 2. Format elements for decimal floating-point to varchar

Format element	Description
0	Each 0 represents a significant digit. Leading zeros in a number are displayed as zeros.
9	Each 9 represents a significant digit. Leading zeros in a number are displayed as blanks.
PL or pl	If the input decimal floating-point expression returns a positive number, a plus sign (+) is added at the specified position.
G or g	The group separator specified by the locale is added at the specified position.
D or d	The decimal delimiter specified by the locale is added at the specified position.
,	A comma is added at the specified position, for example as a group separator.
.	A period is added at the specified position, for example as a decimal point.
S or s	Prefix: If the input decimal floating-point expression returns: A negative number, a leading minus sign (-) is added to the result A positive number, a leading plus sign (+) is added to the result
$	Prefix: A leading dollar sign ($) is added to the result.
MI or mi	Suffix: If the input decimal floating-point expression returns: A negative number, a trailing minus sign (-) is added to the result A positive number, a trailing blank is added to the result
PR or pr	Suffix: If the input decimal floating-point expression returns: A negative number, a leading less than character (<) and a trailing greater than character (>) are added to the result A positive number, a leading space and a trailing space are added to the result

The format elements are case sensitive.

locale-name

A character constant that specifies the locale used to determine the group separator and decimal delimiter.

The specified locale name is not case sensitive and must be a valid locale (SQLSTATE 42815).
For information about valid locales and their naming, see Locale names for SQL and XQuery.
The default is the value of the CURRENT LOCALE LC_TIME special register.

The result is a varying-length character string representation of the input decimal floating-point expression.
The length attribute is 255.
If the string units of the environment or the format string is CODEUNITS32, the string units of the result is CODEUNITS32; otherwise, the string units of the result is OCTETS.
The actual length of the result is determined by the format string, if specified; otherwise, the actual length of the result is the smallest number of characters that can represent the value of the input decimal floating-point expression.
If the resulting string exceeds the length attribute of the result, the result is truncated.

If the value of the input decimal floating-point expression is the special value:

• Infinity, the string "INFINITY" is returned
• sNaN, the string "SNAN" is returned
• NaN, the string "NAN" is returned

If the special value is negative, the first character of the result is a minus sign (-).
The decimal floating-point special value sNaN does not result in an exception when converted to a string.

If the format string does not include any of the format elements MI, S, or PR, and if the value of the input expression is negative, then a minus sign (-) is included in the result; otherwise, a blank is included in the result.

If the number of digits to the left of the decimal point in the input expression is greater than the number of digits to the left of the decimal point in the format string, the result is a string of one or more number sign (#) characters.
If the number of digits to the right of the decimal point in the input expression is greater than the number of digits to the right of the decimal point in the format string, the result is rounded to the number of digits to the right of the decimal point in the format string.
The DECFLOAT rounding mode will not be used.
The rounding behavior of VARCHAR_FORMAT corresponds to a value of ROUND_HALF_UP.

The code page of the result is the code page of the section.

Notes

• Julian and Gregorian calendar:
  For Timestamp to varchar, the transition from the Julian calendar to the Gregorian calendar on 15 October 1582 is taken into account by this function.
• Determinism:
  VARCHAR_FORMAT is a deterministic function.
  However, the following invocations of the function depend on the value of the special register CURRENT LOCALE LC_TIME.
  • Timestamp to varchar, when format-string is not explicitly specified, or when locale-name is not explicitly specified and one of the following statements is true:
    • format-string is not a constant
    • format-string is a constant and includes format elements that are locale sensitive
    These invocations that depend on the value of a special register cannot be used wherever special registers cannot be used (SQLSTATE 42621, 428EC, or 429BX).
• Syntax alternatives:
  TO_CHAR is a synonym for VARCHAR_FORMAT.

Examples

1. Display the names and creation timestamps for all system tables that have names that start with SYSU.

      SELECT VARCHAR(TABNAME, 20) AS TABLE_NAME
      ,      VARCHAR_FORMAT(CREATE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS CREATION_TIME
      FROM   SYSCAT.TABLES
      WHERE  TABNAME LIKE 'SYSU%'

   This example returns the following output:

      TABLE_NAME           CREATION_TIME
      -------------------- -------------------
      SYSUSERAUTH          2000-05-19 08:18:56
      SYSUSEROPTIONS       2000-05-19 08:18:56

2. The variable TMSTMP is defined as a TIMESTAMP and has the value 2007-03-09-14.07.38.123456.
   The following examples show invocations of the VARCHAR_FORMAT function and the resulting string values.
   The data type of each result is VARCHAR(255).

   Function invocation                          Result
   -------------------                          ------
   VARCHAR_FORMAT(TMSTMP,'YYYYMMDDHHMISSFF3')  20070309020738123
   VARCHAR_FORMAT(TMSTMP,'YYYYMMDDHH24MISS')   20070309140738
   VARCHAR_FORMAT(TMSTMP,'YYYYMMDDHHMI')       200703090207

   VARCHAR_FORMAT(TMSTMP,'HH12:MI:SS.MS')      14:07:38.123
   VARCHAR_FORMAT(TMSTMP,'H12:MI:SS.US')      14:07:38.123456

   VARCHAR_FORMAT(TMSTMP,'DD/MM/YY')           09/03/07
   VARCHAR_FORMAT(TMSTMP,'MM-DD-YYYY')         03-09-2007
   VARCHAR_FORMAT(TMSTMP,'J')                  2454169
   VARCHAR_FORMAT(TMSTMP,'Q')                  1
   VARCHAR_FORMAT(TMSTMP,'W')                  2
   VARCHAR_FORMAT(TMSTMP,'IW')                 10
   VARCHAR_FORMAT(TMSTMP,'WW')                 10
   VARCHAR_FORMAT(TMSTMP,'Month','en_US')      March 
   VARCHAR_FORMAT(TMSTMP,'MONTH','en_US')      MARCH 
   VARCHAR_FORMAT(TMSTMP,'MON','en_US')        MAR 
   VARCHAR_FORMAT(TMSTMP,'Day','en_US')        Friday 
   VARCHAR_FORMAT(TMSTMP,'DAY','en_US')        FRIDAY 
   VARCHAR_FORMAT(TMSTMP,'Dy','en_US')         Fri 
   VARCHAR_FORMAT(TMSTMP,'Month','de_DE')      März 
   VARCHAR_FORMAT(TMSTMP,'MONTH','de_DE')      MÄRZ 
   VARCHAR_FORMAT(TMSTMP,'MON','de_DE')        MRZ 
   VARCHAR_FORMAT(TMSTMP,'Day','de_DE')        Freitag 
   VARCHAR_FORMAT(TMSTMP,'DAY','de_DE')        FREITAG 
   VARCHAR_FORMAT(TMSTMP,'Dy','de_DE')         Fr

3. The variable DTE is defined as a DATE and has the following value: 2007-03-09.
   The following examples show several invocations of the function and the resulting string values.
   The data type of each result is VARCHAR(255).

   Function invocation                          Result
   -------------------                          ------
   VARCHAR_FORMAT(DTE,'YYYYMMDD')               20070309
   VARCHAR_FORMAT(DTE,'YYYYMMDDHH24MISS')       20070309000000
   

4. The variables POSNUM and NEGNUM are both defined as DECFLOAT(34), and the value of POSNUM is 1234.56 and the value of POSNUM is -1234.56.
   The following examples show several invocations of the VARCHAR_FORMAT and the resulting string values.
   The data type of each result is VARCHAR(255).

   Function invocation                           Result
   -------------------                           ------
   VARCHAR_FORMAT(POSNUM)                        '1234.56'
   VARCHAR_FORMAT(NEGNUM)                        '-1234.56'
   VARCHAR_FORMAT(POSNUM,'9999.99')              '1234.56'
   VARCHAR_FORMAT(NEGNUM,'9999.99')              '1234.56'
   VARCHAR_FORMAT(POSNUM,'99999.99')             ' 1234.56'
   VARCHAR_FORMAT(NEGNUM,'99999.99')             ' 1234.56'
   VARCHAR_FORMAT(POSNUM,'00000.00')             '01234.56'
   VARCHAR_FORMAT(NEGNUM,'00000.00')             '01234.56'
   VARCHAR_FORMAT(POSNUM,'9999.99MI')            '1234.56 '
   VARCHAR_FORMAT(NEGNUM,'9999.99MI')            '1234.56-'
   VARCHAR_FORMAT(POSNUM,'S9999.99')             '+1234.56'
   VARCHAR_FORMAT(NEGNUM,'S9999.99')             '-1234.56'
   VARCHAR_FORMAT(POSNUM,'9999.99PR')            ' 1234.56 '
   VARCHAR_FORMAT(NEGNUM,'9999.99PR')            '<1234.56>'
   VARCHAR_FORMAT(POSNUM,'S$9,999.99')           '+$1,234.56'
   VARCHAR_FORMAT(NEGNUM,'S$9,999.99')           '-$1,234.56'

   VARCHAR_FORMAT(POSNUM,'99,99,99')           '12,34,56'
   VARCHAR_FORMAT(NEGNUM,'99,99,99')             '-12,34,56'
   VARCHAR_FORMAT(POSNUM,'PL9999.99')            '+1234.56'
   VARCHAR_FORMAT(NEGNUM,'PL9999.99')            '-1234.56'
   VARCHAR_FORMAT(POSNUM,'9999PL')               '1234+'
   VARCHAR_FORMAT(NEGNUM,'9999PL')               '1234'
   VARCHAR_FORMAT(POSNUM,'9999.9')               '1234.6'
   VARCHAR_FORMAT(NEGNUM,'9999.9')               '-1234.6'
   VARCHAR_FORMAT(POSNUM,'9999')                 '1235'
   VARCHAR_FORMAT(NEGNUM,'9999')                 '-1235'
   VARCHAR_FORMAT(POSNUM,'99.99')                '#####'
   VARCHAR_FORMAT(NEGNUM,'99.99')                '#####'
   VARCHAR_FORMAT(POSNUM,'9999D99', 'en_US')     '1234.56'
   VARCHAR_FORMAT(POSNUM,'9999D99', 'fr_FR')     '1234,56'
   VARCHAR_FORMAT(POSNUM,'9G999D99', 'en_US')    '1,234.56'
   VARCHAR_FORMAT(POSNUM,'9G999D99', 'de_DE')    '1.234,56'