VARCHAR_FORMAT

Character to VARCHAR

character-expression

An expression that returns a value that must be a built-in CHAR or VARCHAR data type. 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 value 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.

If character-expression returns graphic data, the CCSID of the result is the character mixed CCSID that corresponds to the graphic argument. If character-expression returns bit data, the result is bit data. Otherwise, the CCSID of the result is the same as the CCSID of character-expression.

Timestamp to VARCHAR

timestamp-expression

An expression that returns a value that must be a DATE or TIMESTAMP, or a valid character string or graphic string representation of a date or timestamp that is not a CLOB or DBCLOB. If the argument is a graphic string representation of a data or timestamp, it is first converted to a character string before evaluating the function.

If timestamp-expression is a DATE or a valid string representation of a date, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00). If the HH12 format element is specified and the time component of the first argument is 24:00:00, the input timestamp value is adjusted to 00:00:00 and the date is incremented by one day.

For the valid formats of string representations of datetime values, see String representations of datetime values.

format-string

An expression that returns a built-in character string or graphic string data type that is not a LOB and has a length attribute that is not greater than 255 bytes. If the value is not a CHAR or VARCHAR data type, it is implicitly cast to VARCHAR before the function is evaluated. Leading and trailing blanks are removed from the string. If the argument returns timestamp data type, the resulting substring must conform to the rules for formatting a timestamp. If expression returns timestamp with a time zone, the resulting substring must conform to the rules for formatting a timestamp with time zone.

The value is a template for how timestamp-expression is to be formatted.

A valid format-string can contain a combination of the format elements listed below. Two format elements can be separated by one or more of the following separator characters.

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

Separator characters can also be specified at the start or end of format-string. format-string can also be an empty string, a string of blanks, or a string of separator characters.

The following table lists the valid format elements that format-string can contain.

Table 1. Valid format elements of format-string

Format element	Description (assuming the default is to return leading zeros)
AM or PM 1	Meridian indicator (morning or evening) without periods. This format element uses the exact strings “AM” or “PM”.
A.M. or P.M. 1	Meridian indicator (morning or evening) with periods. This format element uses the exact strings “A.M.” or “P.M.”.
CC	Century (00-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.
D 1	Day of the week (1-7). 1 is Sunday and 7 is Saturday.
DD	Day of the month (01-31).
DDD	Day of the year (001-366).
FF or FFn	Fractional seconds (0-999999). The number n is used to specify the number of digits to include in the returned value. Valid values for n are 1-12 (FF1-FF12) with no leading zeros. Specifying FF is equivalent to specifying FF6. If the timestamp precision of timestamp-expression is less than what is specified by the format, zero digits are padded onto the right of the specified digits.
HH	Hour of the day (01-12).
HH12	Hour of the day (01-12).
HH24	Hour of the day (00-24).
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 7 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 last four digits of the year based on the ISO week that is returned.
J	Julian date (0000000-9999999).
MI	Minute (00-59).
MM	Month (01-12). January is 01.
MONTH, Month, or month 1, 2	Name of the month in uppercase, sentence case, or lowercase format in English.
MON, Mon, or mon 1, 2	Three-character abbreviated name of the month in uppercase, sentence case, or lowercase format in English.
NNNNNN	Microseconds (000000-999999). This format is equivalent to specifying FF6.
Q	Quarter (1-4). January through March is 1.
RRRR	Year (0000-9999). RRRR behaves the same as YYYY.
RR	Last two digits of the year (00-99). RR behaves the same as YY.
SS	Seconds (00-59).
SSSSS	Seconds since the previous midnight (00000-86400).
TZH	Time zone hour. (-24 to +24, This range accommodates daylight saving time changes.)
TZM	Time zone minute (00–59).
W	Week of the month (1-5). Week 1 starts on the first day of the month and ends on the seventh day.
WW	Week of the year (01-53). Week 1 begins 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	Year (0000-9999).
Notes: This format element is case sensitive. In cases where the format elements are ambiguous, the case insensitive format elements will be considered first. Only these exact spellings and case combinations can be used. If this format element is specified in an invalid case combination an error is returned.

If expression is a TIMESTAMP WITHOUT TIME ZONE value, format-string must not contain TZH or TZM

The result is a representation of timestamp-expression in the format specified by format-string. 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 format-string is interpreted as the longest matching format element in the previous table. 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 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'.

If the first argument is timestamp with time zone, or the second argument is a constant that contains a format element for a time zone, the resulting string contains a timestamp with time zone. Otherwise, the resulting string does not contain a time zone.

The result is the varying-length character string that contains expression in the format that is specified by format-string. If format-string is a constant, The length attribute of the result is the maximum of 255 and the length attribute of format-string. Otherwise, the length attribute is 255. The format-string determines the actual length of the result. The actual length must not be greater than the length attribute of the result.

The result can be null; if the argument is null, the result is the null value.

The CCSID of the result is determined from the context in which the function is invoked. For more information, see Determining the encoding scheme and CCSID of a string.

Decimal floating-point to VARCHAR

decimal-floating-point-expression

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

format-string

An expression that must return a value that is a built-in CHAR, VARCHAR, or numeric data type. If the value is not a CHAR or VARCHAR data type, it is implicitly cast to VARCHAR before evaluating the function. If the supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function. The actual length must not be greater than 254 bytes.

The value is a template for how decimal-floating-point-expression is to be formatted. A format-string must contain a valid combination of the listed format elements according to the following rules:

• A sign format element ('S', 'MI', 'PR') can be specified only one time.
• A decimal point format element can be specified only one time.
• Alphabetic format elements must be specified in upper case
• A prefix format element can only be specified at the beginning of the format string, before any format elements that are not prefix format elements. When multiple prefix format elements are specified they can be specified in any order.
• A suffix format element can only be specified at the end of the format string, after any format elements that are not suffix format elements.
• A comma format element must not be the first format element that is not a prefix format element. There can be any number of comma format elements.
• Blanks must not be specified between format elements. Leading and trailing blanks can be specified but are ignored when formatting the result.

Table 2. Format elements for the VARCHAR_FORMAT (decimal floating-point to VARCHAR) function

Format element	Description
0	Represents a digit. Leading zeros in a number are formatted as zeros.
9	Represents a digit that can be included at the specified location. Leading zeros in a number are formatted as blanks.
S	Prefix If decimal-floating-point-expression is a negative number, a leading minus sign (−) is included at the specified location in the result. If decimal-floating-point-expression is a positive number, a leading plus sign (+) is included in the result.
$	Prefix A dollar sign ($) is included at the specified location in the result.
MI	Suffix If decimal-floating-point-expression is a negative number, a trailing minus sign (−) is included in the result. If decimal-floating-point-expression is a positive number, a trailing blank is included in the result.
PR	Suffix If decimal-floating-point-expression is a negative number, a leading less than character (<) and a trailing greater than character (>) are included in the result. If decimal-floating-point-expression is a positive number, a leading blank and a trailing blank are included in the result.
, (comma)	Each comma represents a group separator that is included at the specified location in the result provided there would be a character to the left of it that is not a prefix character.
. (period/decimal point)	A period represents the decimal point that is included at the specified location in the result.

If format-string is not specified, the function is equivalent to VARCHAR(decimal-floating-point-expression).

The result is a representation of the decimal-floating-point-expression value (which might be rounded) in the format that is specified by format-string. Prior to being formatted, the value of decimal-floating-point-expression is rounded by using the ROUND function, if the number of digits to the right of the decimal point is less than the number of digit format elements ('0' or '9') to the right of the decimal point in format-string. format-string is applied according to the following rules:

• The result does not include any digit characters to the left of the decimal point if all of the following conditions are true:
  • -1 < rounded-input-value < 1
  • format-string does not include a '0' format element to the left of the decimal point
  • format-string includes at least one digit format element ('0' or '9') to the right of the decimal point
• The result includes a single 0 character immediately before the implicit or explicit decimal point if all of the following conditions are true:
  • The value of rounded-input-value is 0 or -0
  • format-string includes only the '9' digit format elements to the left of the implicit or explicit decimal point
  • format-string does not include any digit format elements to the right of the decimal point
• If format-string includes both '0' and '9' format elements to the left of the decimal point, the position of the first digit format element from the left side of the format string determines the presence of leading blanks or zeroes. All '9' format elements specified after the leftmost '0' format element to the left of the implicit or explicit decimal point are treated the same as if a '0' format element had been specified. For example, the format-string value '99099' is the same as the value '99000'.
• If the number of digits to the right of the decimal point in rounded-input-value is less than the number of digit format elements to the right of the decimal point in format-string, the result includes the number of digit characters to the right of the decimal point that corresponds to the number of digit format elements to the right of the decimal point in format-string, padded to the right with zeros.
• If the number of digits to the left of the decimal point in rounded-input-value is greater than the number of digit format elements to the left of the decimal point in format-string, the result is a string of number sign (#) characters that matches the length that format-string produces in the result for valid values.
• If the value of rounded-input-value represents any of the positive or negative special values, Infinity, sNaN, or NaN, the string 'INFINITY', 'SNAN', 'NAN', '-INFINITY', '-SNAN', or '-NAN' is returned without using the format that is specified by format-string. The decimal floating-point special value sNaN does not result in an exception when converted to a string.
• If format-string does not include any of the sign format elements 'S', ''MI', or 'PR', and the value of rounded-input-value is negative, a minus sign (−) is included in the result. Otherwise, a blank is included in the resulting string. The minus sign or blank immediately precedes the first digit of the result to the left of the decimal point, or the decimal point if there are no digits to the left of the decimal point.

The result is a varying-length character string representation of rounded-input-value. If a single argument is specified the length attribute is 42. Otherwise the length attribute is 254. The actual length of the result is determined by format-string, if specified. Otherwise, the actual length of the result is the smallest number of characters that can represent the value of rounded-input-value. If the resulting string exceeds the length attribute of the result, the result will be truncated.

The CCSID of the result is determined from the context in which the function is invoked. For more information, see Determining the encoding scheme and CCSID of a string

Notes

Julian and Gregorian calendar:

For timestamp to a varying length character string, 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.

Using the 'D', 'Y', and 'y' format elements:

DB2® for z/OS® does not support the 'DY', 'dy', and 'Dy' format elements that are supported by other platforms. If 'DY' or 'Dy' is specified in the format string, it is interpreted as the 'D' format element followed by the 'Y' or 'y' format element. This behavior might change in a future release. To ensure that a 'D' followed by 'Y' or 'y' is interpreted as two separate format elements, include a separator character after the 'D' format element.

Syntax alternatives:

TO_CHAR can be specified as a synonym for VARCHAR_FORMAT.