An expression that resolves to a conversion code string. This conversion code specifies the type of conversion to perform. Conversion is from external format to internal format. Conversion codes are not case-sensitive. For a complete list of conversion codes, refer to the Conversion Codes table in the MultiValue Basic Quick Reference.
The ICONV function is a general-purpose conversion function used to convert from external (output) format to internal (storage) format. The type of conversion is specified by a code string that is specific to the type of data to be converted. These conversion codes are as follows:
Time conversion: Internal times are stored as the number of seconds elapsed since midnight. ICONV accepts fractional seconds. You can specify the time in 24-hour format (22:45:33) or 12-hour format with the AM/PM suffixes specified for your locale (10:45:33PM) . These suffixes are not case-sensitive. In 12-hour format, you can specify noon or midnight as either 0 or 12. You can specify a number of hours greater than 23 (27:45:33) and ICONV calculates the total number of elapsed seconds; you cannot specify a number of minutes or seconds greater than 59. You can use any non-numeric character as a time delimiter (22.45.33); however, you cannot use a period (.) for both the time delimiter and to indicate fractional seconds. You can prefix a time value with a minus sign; ICONV prefixes the returned internal time with a minus sign.
Date conversion: Takes as input date strings in both numeric formats (9/22/2010, 20100922) and text formats (Oct 22, 2010). If you omit the year portion of the date, it defaults to the current year. If you input a six-digit numeric string without separators, the string is parsed as YYMMDD. These external dates are converted to an internal date integer. Internal dates are stored as the number of days elapsed since December 31, 1967. Dates prior to this are stored using a negative number of days. The earliest permitted date is December 31, 1840 which is represented internally as -46385.
"DI" can be used to convert dates in numeric format or dates in text format using the NLS locale settings. For example Feb 22, 2010 or 22 FEBRUARY 2010. OCONV and implicit conversion can convert numeric dates using DI, but cannot convert text format dates.
"D" / "DI"
Letter case conversion: MCL converts all letters to lower case. MCU converts all letters to upper case. You can also use OCONV to perform letter case conversions.
"MCL" / "MCU"
Mask Character Alphabetic: converts ostring by removing all non-alphabetic characters, returning only the alphabetic characters.
Mask Character Both Alphabetic and Numeric: converts ostring by removing all punctuation characters, returning only the alphabetic and numeric characters.
Mask Character Numeric: converts ostring by removing all non-numeric characters, returning only the number characters 0 through 9. (Note that plus and minus signs and the decimal point are removed.)
Character-to-code conversion: all characters in the input string are converted to their corresponding hexadecimal integer codes. Use MCXA or "MCX" to output 8-bit code values; use MCXW to output 16-bit (wide) code values.
"MCXA" / "MCX" or "MCXW"
Code-to-character conversion: one or more hexadecimal codes in the input string are converted to their corresponding characters. Use MCAX or "MX" for two-byte code input values; use MCWX for four-byte (wide) code input values.
"MCAX" / "MX" or "MCWX"
Masked decimal conversion: Converts a fractional number to an integer by moving the decimal point to the right, rounding remaining fractional digits, and resolving numeric display characters such as currency symbols. The returned value is either a positive integer or a negative integer with a leading minus sign.
If only n is specified, this positive integer value shifts the decimal point that number of places to the right. Any remaining fractional digits are rounded to the resulting integer value. Right side zero padding is added as needed. If both n and m are specified, it is the m value that specifies the number of places to shift the decimal place. (In fact, the m value always specifies the decimal shift; when m is omitted it defaults to the n value.)
Masked decimal conversion automatically removes the following formatting characters: leading zeros; a single leading or trailing + sign; a leading $ currency symbol; commas used as numeric group separators (any comma appearing between two number characters); a trailing decimal point. Masked decimal conversion automatically handles negative numbers as follows: a single leading minus sign is retained; a single trailing minus sign converts to a leading minus sign; a positive number enclosed in parentheses converts to a negative number with a leading minus sign. Note that leading sign characters must appear after the currency symbol. An ostring containing any other characters or character sequences always returns as 0. An empty string ostring value returns an empty string.
Group (delimited substring) extraction: a substring is extracted from istring, based on a specified delimiter character (d) found in istring that indicates the stopping point. This delimiter cannot be a number character or a dynamic array level delimiter character. The optional s integer specifies the number of delimiters to skip from the beginning of the string before starting the extract. The default is to start at the beginning of the string. The n integer specifies the number of delimiters to count in performing the extract. If n is larger than the number of d delimiters, the extract continues to the end of the string.
Length conversion: "L" or "L0": returns the number of characters in istring.
"Ln": returns the value of istring if n is the exact number of characters in istring. Otherwise returns the empty string. n must be a positive non-zero integer.
"Ln-m" or "Ln,m": returns the value of istring if the number of characters in istring is in the range n through m (inclusive). Otherwise returns the empty string. n can be specified as zero in this syntax.
The DATE and TIME functions return internal format values. The TIMEDATE function returns external format values.
You can specify the default date and time formats using Caché NLS. Because of operational differences between MV and Caché NLS in the handling of month names, your NLS default date format must represent months as integers.
You can use the STATUS function to determine the success of an ICONV conversion. The following status values are supported: 0=successful conversion; 1=invalid ostring; 2=invalid code value.
The ICONV function converts from external format to internal format. The OCONV function converts from internal format to external format. Note that the MCDX/MCXD, MCAX/MCXA, and MCWX/MCXW code pairs have the opposite meanings in OCONV, reversing the ICONV operation.
You can use the ICONVS function to convert the elements of a dynamic array from external format to internal format.
The following example show date conversions from external to internal format. All of these ICONV functions return the internal date 14143:
Multiple sign characters and other non-numeric characters are invalid and return 0.
In Caché MultiValue and most emulations, an invalid ostring date value (with code D or DI) returns the empty string. An invalid ostring numeric value (with code ML, MR, MD) returns 0. In UniData emulation an invalid value for these date and numeric codes returns the ostring value unchanged.
In Caché MVBasic and most emulations, a six-digit numeric string without separators is parsed as YYMMDD. Thus, the following are equivalent: