VARCHAR_FORMAT
The VARCHAR_FORMAT function returns a character string representation of the first argument in the format indicated by the optional format-string.
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 to Varchar
- string-expression
- An expression that returns a value that is a built-in character-string or graphic-string data type.
Timestamp to Varchar
- timestamp-expression
- An expression that returns a value of one of the following built-in data types: a date, a timestamp, a character string, or a graphic string. If the argument
is a DATE, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight
(00.00.00).
If timestamp-expression is a character or graphic string, the value of timestamp-expression must be a valid string representation of a date or timestamp. It is first converted to a TIMESTAMP(12) value. For the valid formats of string representations of timestamps, see String representations of datetime values.
If the argument is a string, the format-string argument must also be specified.
- format-string
- An expression that returns a built-in character string data type
or graphic string data type. If the value is not a CHAR or VARCHAR
data type, it is implicitly cast to VARCHAR before evaluating the
function. format-string contains a template
of how timestamp-expression is to be formatted. The resulting value may contain characters in any case. A
valid format is any combination of the formats listed below optionally
separated by valid separators. Valid separators are:
- minus sign (-)
- period (.)
- slash (/)
- comma (,)
- apostrophe (′)
- semicolon (;)
- colon (:)
- blank ( )
| Format | Unit |
|---|---|
| AM or PM 1, 2 | Meridian indicator (morning or evening) without periods. The meridian indicator that is returned is based on the language used for messages in the job. This meridian indicator is retrieved from message CPX9035 in message file QCPFMSG in library *LIBL. |
| A.M. or P.M. 1, 2 | Meridian indicator (morning or evening) with periods. This format element uses the exact strings ‘A.M.' or ‘P.M.' and is independent of the language used for messages in the job. |
| 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. |
| DAY, Day, or day 1, 2 | Name of the day in uppercase, titlecase, or lowercase format. The name of the day that is returned is based on the language used for messages in the job. This name of the day is retrieved from message CPX9034 in message file QCPFMSG in library *LIBL. |
| DY, Dy, or dy 1, 3 | Abbreviated name of the day in uppercase, titlecase, or lowercase format. The abbreviated name of the day is retrieved from message CPX9039 in message file QCPFMSG in library *LIBL. |
| D 1 | Day of week (1-7), where 1 is Sunday. |
| DD | Day of month (01-31). |
| DDD | Day of year (001-366). |
| FF or FFn | Fractional seconds (0-999999999999). The number n is used to specify the number of digits to include in the value returned. Valid values for n are 1-12. The default is 6. |
| 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. |
| ID | ISO day of week (1-7), where 1 is Monday and 7 is Sunday |
| IW | ISO week of 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 |
| I | ISO year (0-9). The last digit of the year based on the ISO week that is returned. |
| 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 year based on the ISO week that is returned. |
| J | Julian date (0000000-9999999). |
| MI | Minute (00-59). |
| MM | Month (01-12). |
| MONTH, Month, or month 1, 3 | Name of the month in uppercase, titlecase, or lowercase format. The name of the month that is returned is based on the language used for messages in the job. This name of the month is retrieved from message CPX3BC0 in message file QCPFMSG in library *LIBL. |
| MON, Mon, or mon 1, 3 | Abbreviated name of the month in uppercase, titlecase, or lowercase format. The name of the month that is returned is based on the language used for messages in the job. This name of the month is retrieved from message CPX8601 in message file QCPFMSG in library *LIBL. |
| MS |
Milliseconds (000-999). Same as FF3. |
| NNNNNN | Microseconds (000000-999999). Same as FF6. |
| Q | Quarter (1-4). |
| 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 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), 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 | 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.
- The AM and PM set of meridian indicators can be used interchangeably in the format-string, as can A.M. and P.M. The result string will contain the appropriate meridian indicator for the actual time value.
- 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.
Examples of valid format strings are:
'HH24-MI-SS'
'HH24-MI-SS-NNNNNN'
'YYYY-MM-DD'
'YYYY-MM-DD-HH24-MI-SS'
'YYYY-MM-DD-HH24-MI-SS-NNNNNN'
'FF3.J/Q-YYYY'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 format element that matches an element in the previous table. 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 longest element that matches an element from the previous table, 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.
If format-string is not specified, timestamp-expression is returned as the varying-length character string representation of the timestamp.
The data type of the result is varying-length character or varying-length graphic based on the data type of the format-string. The length attribute of the result is the maximum of 255 and the length attribute of the format-string. format-string also determines the actual length of the result. The actual length must not be greater than the length attribute of the result.
The CCSID of the result is same as the CCSID of the format-string. If format-string is not specified, the CCSID of the result is the default SBCS CCSID at the current server.
Numeric to Varchar
- numeric-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 returns a built-in character string, graphic
string, 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. format-string contains a template
of how numeric-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. When multiple suffix format elements are specified they can be specified in any order.
- A comma or G format element must not be the first format element that is not a prefix format element. There can be any number of comma or G format elements.
- Blanks must not be specified between format elements. Leading and trailing blanks can be specified but are ignored when formatting the result.
| 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. Only group separators that have at least one digit to the left of the separator are generated. |
| S | Prefix: If numeric-expression is a negative number, a leading minus sign (-) is included in the result. If numeric-expression is a positive number, a leading plus sign (+) is included in the result. |
| $ | Prefix: A leading dollar sign ($) is included in the result. |
| MI | Suffix: If numeric-expression is a negative number, a trailing minus sign (-) is included in the result. If numeric-expression is a positive number, a trailing blank is included in the result. |
| PR | Suffix: If numeric-expression is a negative number, a leading less than character (<) and a trailing greater than character (>) are included in the result. If numeric-expression is a positive number, a leading space and a trailing space are included in the result. |
| , | Specifies that a comma be included in that location in the result. This comma is used as a group separator. |
| . | Specifies that a period be included in that location in the result. This period is used as a decimal point. |
| L | Prefix or Suffix: Specifies that the local currency symbol be included in that location in the result. The currency symbol is retrieved from message CPX8416 in message file QCPFMSG in library *LIBL. |
| D | Specifies that the local decimal point character be included in that location in the result. The decimal character is retrieved from message CPX8416 in message file QCPFMSG in library *LIBL. |
| G | Specifies that the local group separator character be included in that location in the result. If the local decimal character as retrieved from message CPX8416 in message file QCPFMSG in library *LIBL is a period, the group separator will be a comma. If the local decimal character is a comma, the group separator will be a period. |
If format-string is not specified, the function is equivalent to VARCHAR(DECFLOAT(numeric-expression)).
The result
is a representation of the numeric-expression value (which
might be rounded) in the format that is specified by format-string.
Prior to being formatted, the value of numeric-expression is
rounded by using the ROUND function if the number of digits to the
right of the decimal point is greater than the number of digit format
elements ('0' or '9') to the right of the decimal point in format-string. format-string is
applied to this rounded-input-value 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 string representation of rounded-input-value. The data type of the result is varying-length character or varying-length graphic based on the data type of the format-string. 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 the same as the CCSID of the format-string. If format-string is not specified, the CCSID of the result is the default SBCS CCSID at the current server.
Note
Syntax alternatives: TO_CHAR is a synonym for VARCHAR_FORMAT.
Examples
Example: Timestamp to VARCHAR
- Set the character variable TVAR to a string representation
of the timestamp value of RECEIVED from CORPDATA.IN_TRAY, formatted
as YYYY-MM-DD HH24:MI:SS.
Returns the string:SELECT VARCHAR_FORMAT(RECEIVED,'YYYY-MM-DD HH24:MI:SS') INTO :TVAR FROM CORPDATA.IN_TRAY WHERE SOURCE = 'CHAAS'
Assuming that the value in the RECEIVED column is one second before the beginning of the year 2000 (December 31, 1999 at 23:59:59pm), the following string is returned:1988-12-22 14:07:21
The result would be different if HH12 had been specified instead of HH24 in the format string:1999-12-31 23:59:591999-12-31 11:59:59
Example: Timestamp to VARCHAR
- Assume that the variable TMSTAMP is defined as a TIMESTAMP
and has the following value: 2007-03-09-14.07.38.123456. The following
examples show several invocations of the function and the resulting
string values. The result data type in each case is VARCHAR(255).
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 VARCHAR_FORMAT(TMSTAMP,'Month') March VARCHAR_FORMAT(TMSTAMP,'MONTH') MARCH VARCHAR_FORMAT(TMSTAMP,'MON') MAR
Example: Timestamp to VARCHAR
- Assume that the variable DTE is defined as a DATE and has
the value of '2007-03-09'. The following examples show several invocations
of the function and the resulting string values. The result data type
in each case is VARCHAR(255):
Function invocation Result ------------------- ------ VARCHAR_FORMAT(DTE,'YYYYMMDD') 20070309 VARCHAR_FORMAT(DTE,'YYYYMMDDHH24MISS') 20070309000000
Example: Timestamp to VARCHAR
- Format the hour of the specified string representation of a timestamp:
The previous SELECT statement returns the following values:SELECT VARCHAR_FORMAT(TIMESTAMP('1979-04-07-14.00.00.000000'), 'HH'), VARCHAR_FORMAT(TIMESTAMP('1979-04-07-14.00.00.000000'), 'HH12') VARCHAR_FORMAT(TIMESTAMP('1979-04-07-14.00.00.000000'), 'HH24'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-00.00.00.000000'), 'HH'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-12.00.00.000000'), 'HH'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-24.00.00.000000'), 'HH'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-00.00.00.000000'), 'HH12'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-12.00.00.000000'), 'HH12'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-24.00.00.000000'), 'HH12'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-00.00.00.000000'), 'HH24'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-12.00.00.000000'), 'HH24'), VARCHAR_FORMAT(TIMESTAMP('2000-01-01-24.00.00.000000'), 'HH24') FROM SYSIBM.SYSDUMMY1;'02' '02' '14' '12' '12' '12' '12' '12' '12' '00' '12' '24'
Example: Numeric to VARCHAR
- Assume that the variables POSNUM and NEGNUM are defined as DECFLOAT(34)
and have the following values: '1234.56' and '-1234.56', respectively.
The following examples show several invocations of the function and
the resulting string values.
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'